Extract multiple values from an array in JSON in BigQuery-CodePudding

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?

CodePudding user response：

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