SQL, parse json data #eg28

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:

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

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

Source

SPL open source address