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

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!

Answer

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)