BigQuery extract one attribute across a JSON array of JSON objects

Question

I want to modify the following query to extract the value attribute from the objects in the JSON array and output a table as follows:

"value1", "value2"

However my query currently outputs the whole JSON object for each entry in the array.

I want to do this so that I can use it as a sub query for an IN expression.

SELECT valueJson
FROM (
  (
    SELECT JSON_EXTRACT_ARRAY(values) AS valueJson
    FROM (
      SELECT JSON_QUERY(tag, '$.values') AS values
      FROM UNNEST(JSON_EXTRACT_ARRAY(
        JSON 
'[{"active":true,"key":"key1","values":[{"active":true,"value":"value1"}]},{"active":true,"key":"key2","values":[{"active":true,"value":"value2"}]}]')) AS tag
    )
  )
)

Thanks!

Answer 1

Consider below approach. It is clean and skinny and thus quite self-explanatory

with your_data as (
  select JSON '[{"active":true,"key":"key1","values":[{"active":true,"value":"values1"}]},{"active":true,"key":"key2","values":[{"active":true,"value":"value2"},{"active":true,"value":"value3"}]}]' json
)
select val.value
from your_data, 
unnest(json_extract_array(json)) el,
unnest(json_extract_array(el.values)) val   

with output