Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

BigQuery extract one attribute across a JSON array of JSON objects

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I came up with this, but happy to accept another answer with explanations or improvement:

    SELECT
  JSON_EXTRACT_SCALAR(valueJson, '$.value') AS value
FROM (
  SELECT valueJson
  FROM (
    SELECT JSON_QUERY(tag, '$.values') AS valuesArray
    FROM UNNEST(JSON_EXTRACT_ARRAY( 
      JSON '[{"active":true,"key":"key1","values":[{"active":true,"value":"values1"}]},{"active":true,"key":"key2","values":[{"active":true,"value":"value2"},{"active":true,"value":"value3"}]}]' )) AS tag )
JOIN UNNEST(JSON_EXTRACT_ARRAY(valuesArray)) AS valueJson )
WHERE JSON_EXTRACT_SCALAR(valueJson, '$.value') IS NOT NULL

  2. 0

    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

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search