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:

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

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!

CodePudding user response：

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

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)

CodePudding user response：

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;