Extract multiple values from an array in JSON in BigQuery

Question

I have a JSON in my database table like the following one, and let's say the column that contains a JSON file called favorites.

{
    "info": {
        "music": [{"Year":2021,"Name":"Stay","Singer":"Justin Bieber"},
                  {"Year":2015,"Name":"Love Yourself","Singer":"Justin Bieber"},
                  {"Year":2003,"Name":"Crazy In Love","Singer":"Beyonce"}
                 ],
        "movie": [{"Year":2018,"Name":"Green Book","Director":"Peter Farrelly"},
                  {"Year":2007,"Name":"Lust, Caution","Director":"Ang Lee"}
                 ]
             }
}

I wanted to select all values from tags and my expected table would be like as following:

-----------------------------------------------------------------------------
|             Name                    |                      Singer         |
----------------------------------------------------------------------------
|   Stay,Love Yourself,Crazy In Love  |  Justin Bieber,Justin Bieber,Beyonce|
-----------------------------------------------------------------------------

I already know how to get the first value in an array with JSON_QUERY(json_col,'$.info.music[0].Name'), but I would like to extracted all the names or singers into one single column, and some arrays may have multiple items. Doe anyone have any suggestions?

Mikhail Berlyant · Accepted Answer · 2021-09-30 04:31:41Z

3

Consider below approach

select 
  array(select json_extract_scalar(x, '$.Name') from unnest(json_extract_array(json_col, '$.info.music') || json_extract_array(json_col, '$.info.movie')) x) Name,
  array(select json_extract_scalar(x, '$.Singer') from unnest(json_extract_array(json_col, '$.info.music')) x) Singer
from data

if applied to sample data in your question - output is

I just realized - you wanted comma separated list - so consider below then

select 
  (select string_agg(json_extract_scalar(x, '$.Name')) from unnest(json_extract_array(json_col, '$.info.music') || json_extract_array(json_col, '$.info.movie')) x) Name,
  (select string_agg(json_extract_scalar(x, '$.Singer')) from unnest(json_extract_array(json_col, '$.info.music')) x) Singer
from data

with output

answered Sep 30, 2021 at 4:31

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

monckeyyL Over a year ago

What's the x in json_extract_scalar()?

Mikhail Berlyant Over a year ago

just alias for unnest() - see at the ed of respective lines. name does not matter can be any valid name, like abc or xyz or anything else

Mikhail Berlyant Over a year ago

1) json_extract_array - returns array of jsons 2)unnest() returns element of that array one by one and is being aliased as x - so x is essentially also json 3) now json_extract_scalar - extract from x whatever xpath is : hope this helps to understand the logic of solution :o)

monckeyyL Over a year ago

Thank you so much! I found that the first way works much better. But I have a question regarding COUNT(). Let's say I will count the number of people listen to different songs, since one row has multiple sub-rows, will that affect the count(*) group by names?

Mikhail Berlyant Over a year ago

glad it helped. please consider also voting up the answer if it helped - this is equally important here on SO and motivates us to answer your next questions :o)

|

Mr.Batra · Accepted Answer · 2021-09-30 08:31:34Z

Another sol. One can use ARRAY_TO_STRING if you don't want aggregation.

with data as
(
select
    """
    {
"info": {
        "music": [{"Year":2021,"Name":"Stay","Singer":"Justin Bieber"},
                  {"Year":2015,"Name":"Love Yourself","Singer":"Justin Bieber"},
                  {"Year":2003,"Name":"Crazy In Love","Singer":"Beyonce"}
                 ],
        "movie": [{"Year":2018,"Name":"Green Book","Director":"Peter Farrelly"},
                  {"Year":2007,"Name":"Lust, Caution","Director":"Ang Lee"}
                 ]
             }
}
""" as _json
)

select array_to_string(
    array(
    select  json_extract_scalar(x,"$.Name")
    from  data,  
          unnest(json_extract_array(_json,"$.info.music")) as x
),"," 
) as Name, array_to_string(
    array(
      select  json_extract_scalar(x,"$.Singer")
    from  data,  
        unnest(json_extract_array(_json,"$.info.music")) as x
),","
) as Singer

Reesult

Collectives™ on Stack Overflow

Extract multiple values from an array in JSON in BigQuery

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related