...

SQL, parse json data

 

In Google BigQuery database, data table stores a number of multilayer JSON strings. Below is one of them:

[{"active":true,"key":"key1","values":[{"active":true,"value":"value1"}]},{"active":true,"key":"key2","values":[{"active":true,"value":"value2"}]}]

We need to parse the JSON strings and get all values under values.values field. Take the above JSON string as an example, the expected result is value1,value2.

Write the following SPL code:

A

1

=bigQuery1.query("select jsonfield from data where id=1")

2

=json(A1.jsonfield).(values.value)

A1: Run the simple SQL statement to retrieve JSON strings.

A2: Parse the multilayer strings and get all values of values.value.

Source:https://stackoverflow.com/questions/78427141/bigquery-extract-one-attribute-across-a-json-array-of-json-objects


 
 •  46 View  •