How to convert an array of key values to columns in BigQuery / GoogleSQL?

user_1696153 0
Viewed 409

I have an array in BigQuery that looks like the following:

SELECT params FROM mySource;

[
  {
    key: "name",
    value: "apple"
  },{
    key: "color",
    value: "red"
  },{
    key: "delicious",
    value: "yes"
  }
]

Which looks like this:

params
[{ key: "name", value: "apple" },{ key: "color", value: "red" },{ key: "delicious", value: "yes" }]

How do I change my query so that the table looks like this:

name color delicious
apple red yes

Currently I'm able to accomplish this with:

SELECT 
         (
           SELECT p.value
           FROM UNNEST(params) AS p
           WHERE p.key = "name"
         ) as name,
         (
           SELECT p.value
           FROM UNNEST(params) AS p
           WHERE p.key = "color"
         ) as color,
         (
           SELECT p.value
           FROM UNNEST(params) AS p
           WHERE p.key = "delicious"
         ) as delicious,
FROM mySource;

But I'm wondering if there is a way to do this without manually specifying the key name for each. We may not know all the names of the keys ahead of time.

Thanks!

google-bigquery sql
2 Answers

Consider below approach

select * except(id) from (
  select to_json_string(t) id, param.* 
  from mySource t, unnest(parameters) param
)
pivot (min(value) for key in ('name', 'color', 'delicious'))

if applied to sample data in your question - output is like below

enter image description here

As you can see - you still need to specify key names but whole query is much simpler and more manageable

Meantime, above query can be enhanced with use of EXECUTE IMMEDIATE where list of key names is auto generated. I have at least few answers with such technique, so search for it here on SO if you want (I just do not want to make a duplicates here)

user_52219440

Here is my try based on Mikhail's answer here

--DDL for sample view 

create or replace view sample.sampleview
as

with _data
as
(
    select 1 as id,
    array (
        select struct(
            "name" as key,
            "apple" as value
        ) 
        union all
        select struct(
            "color" as key,
            "red" as value
        )
        union all 
        select struct(
            "delicious" as key,
            "yes" as value
        )
    ) as _arr  
    union all 
    select 2 as id,
    array (
            select struct(
                "name" as key,
                "orange" as value
            ) 
            union all
            select struct(
                "color" as key,
                "orange" as value
            )
            union all 
            select struct(
                "delicious" as key,
                "yes" as value
            )
    )
     
    
)
select * from _data

Execute immediate

declare sql string;

set sql = 
            (
                select 
                    concat(
                            "select id,",
                            string_agg(
                                concat("max(if (key = '",key,"',value,NULL)) as ",key)
                            ),
                            ' from sample.sampleview,unnest(_arr) group by id'
                    )
                from (
                    select key from
                    sample.sampleview,unnest(_arr)
                    group by key
                    )
                
            );
execute immediate   sql;
user_60622920
Related