Question 109392 in 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

SQL: From JSON object select value when key is equal to search term

In Google BigQuery I have a column in a table which contains JSON objects similar to this one

select JSON '[{"id":"A","value":"1"},{"id":"B","value":"2"},{"id":"C","value":"John"},{"id":"D","value":null},{"id":"E","value":"random"}]' as JSON_

I want to create a new column with the value of the JSON object where the id is equal to "B".
One way to solve this is as follows. But it only works if it is always the second object which has id = "B".

With table_ as (
select JSON '[{"id":"A","value":"1"},{"id":"B","value":"2"},{"id":"C","value":"John"},{"id":"D","value":null},{"id":"E","value":"random"}]' as JSON_
)

select JSON_EXTRACT(JSON_, '$[1].value') as value_2
from table_

Is there a more reliable method how to extract the json object value according to the specific id?

Thank you for your time and help!

Tags:

2

Answers


  1. 0

    bigquery is great for handle jsons and unstructured data, but sometimes its a pain to find the correct way

    in your case, we need to:

    1. Transform the json array to a array of jsons
    2. Explode the array to each element have it own row
    3. Get the json values of each json row

    the query is the following

    with

table_ as (
select
  json '[{"id":"A","value":"1"},{"id":"B","value":"2"},{"id":"C","value":"John"},{"id":"D","value":null},{"id":"E","value":"random"}]' as json_array_column
),

json_array_to_array_of_jsons as (
select
  json_extract_array(table_.json_array_column, '$.') as array_of_jsons
from
  table_
),

exploded_array_to_lines as (
select
  json_value(json_column, '$.id') as id,
  json_value(json_column, '$.value') as value
from
  json_array_to_array_of_jsons,
  unnest (array_of_jsons) as json_column
),

result as (
select
  *
from
  exploded_array_to_lines
where
  id = 'B'
)

select * from result
    Login or Signup to reply.
  2. 0

    You can consider below if you’re interested only in an id B.

    SELECT (SELECT STRING(j.value) 
          FROM UNNEST(JSON_QUERY_ARRAY(JSON_)) j 
         WHERE STRING(j.id) = 'B'
       ) AS value_2
  FROM table_;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search