Convert ARRAY<STRUCT> to multiple columns in BigQuery SQL

Question

I'm trying to convert Array< struct > to multiple columns. The data structure looks like:

column name: Parameter
[
  -{
      key: "Publisher_name"
      value: "Rubicon"
   }
  -{
      key: "device_type"
      value: "IDFA"
   }
  -{
      key: "device_id"
      value: "AAAA-BBBB-CCCC-DDDD"
   }
] 

What I want to get:

publisher_name  device_type  device_id
Rubicon         IDFA         AAAA-BBBB-CCCC-DDDD

I have tried this which caused the duplicates of other columns.

select h from table unnest(parameter) as h

BTW, I am very curious why do we want to use this kind of structure in Bigquery. Can't we just add the above 3 columns into table?

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  (SELECT value FROM UNNEST(Parameter) WHERE key = 'Publisher_name') AS Publisher_name,
  (SELECT value FROM UNNEST(Parameter) WHERE key = 'device_type') AS device_type,
  (SELECT value FROM UNNEST(Parameter) WHERE key = 'device_id') AS device_id
FROM `project.dataset.table`

You can further refactor code by using SQL UDF as below

#standardSQL
CREATE TEMP FUNCTION getValue(k STRING, arr ANY TYPE) AS
((SELECT value FROM UNNEST(arr) WHERE key = k));
SELECT 
  getValue('Publisher_name', Parameter) AS Publisher_name,
  getValue('device_type', Parameter) AS device_type,
  getValue('device_id', Parameter) AS device_id
FROM `project.dataset.table`