Bigquery unnest and pivot column

I have a very big table in BigQuery formatted as follows:

I need to re-format it as follows:

I tried to use this but couldn't make it work in my table

https://stackoverflow.com/questions/63989161/how-to-unnest-and-pivot-two-columns-in-bigquery

One important thing in my table is that, like in the above table, the ids repeat in different rows but have different values for the same keys. Where the ids repeat I want to still have all incidences of the ids, like in the formatted table example.

Also I put only 4 keys here but I actually have 50, so the least manual it is, the better. If the only way is writing each column manually then I'll still do it though.

Does anyone know how to do it? I basically need to turn every key into a new column with it's corresponding value associated with the ids, but I don't know how to do it.

Thank you very much!

In order to re-format it, firstly you need to merge column.value struct into one single column value. This can be easily done using COALESCE function since usually there is only one non-null value in column.value struct in Google Analytics.

COALESCE(
  value.string_value, 
  '' || value.int_value, -- to covert int64 to string
  '' || value.float_value,
  '' || value.boolean_value
) value,

After that, you can simply reshape it using PIVOT query.

SELECT * FROM (
  SELECT t.* EXCEPT(column),
         key,
         COALESCE(
           value.string_value, 
           '' || value.int_value,
           '' || value.float_value,
           '' || value.boolean_value
         ) value,
    FROM sample_table t, UNNEST(column)
) PIVOT (ANY_VALUE(value) FOR key IN ('key1', 'key2', 'key3', 'key4'));

Query results

sample_table

WITH sample_table AS (
SELECT 1 row,
       'id1' id,
       [STRUCT('key1' AS key, STRUCT('aa' AS string_value, INT64(null) AS int_value, FLOAT64(null) AS float_value, BOOL(null) AS boolean_value) AS value),
        STRUCT('key2' AS key, STRUCT(null AS string_value, null AS int_value, null AS float_value, false AS boolean_value) AS value),
        STRUCT('key3' AS key, STRUCT('fa' AS string_value, null AS int_value, null AS float_value, null AS boolean_value) AS value),
        STRUCT('key4' AS key, STRUCT(null AS string_value, null AS int_value, null AS float_value, true AS boolean_value) AS value)] column
 UNION ALL
SELECT 2 row, 'id1' id,
       [STRUCT('key1' AS key, STRUCT('ab' AS string_value, INT64(null) AS int_value, FLOAT64(null) AS float_value, BOOL(null) AS boolean_value) AS value),
        STRUCT('key2' AS key, STRUCT(null AS string_value, null AS int_value, null AS float_value, false AS boolean_value) AS value),
        STRUCT('key3' AS key, STRUCT('gf' AS string_value, null AS int_value, null AS float_value, null AS boolean_value) AS value),
        STRUCT('key4' AS key, STRUCT(null AS string_value, null AS int_value, null AS float_value, false AS boolean_value) AS value)] column
 UNION ALL
SELECT 3 row, 'id2' id,
       [STRUCT('key1' AS key, STRUCT('af' AS string_value, INT64(null) AS int_value, FLOAT64(null) AS float_value, BOOL(null) AS boolean_value) AS value),
        STRUCT('key2' AS key, STRUCT(null AS string_value, null AS int_value, null AS float_value, true AS boolean_value) AS value),
        STRUCT('key3' AS key, STRUCT('fa' AS string_value, null AS int_value, null AS float_value, null AS boolean_value) AS value),
        STRUCT('key4' AS key, STRUCT(null AS string_value, null AS int_value, null AS float_value, false AS boolean_value) AS value)] column
)

You can further generalize it with a dynamic query if you have many keys in your table refering to the link in your question or below.

• https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-bigquery