BigQuery convert arrays of key:val items to table of rows where the columns are the keys

Avishay28 0
Viewed 20

Say I have this arrays in bigquery (the result of splitting a string)

["user_id: 1342", "foo: bar", "baz: xxx"]
["user_id: 4312", "foo: ds", "baz: dss"]
["user_id: 512", "foo: fsf", "baz: aas"]
....

I wonder if there is an option get result as a table that looks like this:

user_id foo baz
1342 bar xxx
4312 ds dss
512 fsf aas
... ... ...

What I'm trying to do is to parse logs message that have this structure: some log text... user_id: 123, foo: bar, baz: xxx

I already parsed the string to have the initial arrays by using SUBSTR, STRPOS and SPLIT, but I can't figure how to move the keys to the column and have the final result.

google-bigquery
1 Answers

Consider below PIVOT query.

WITH sample_table AS (
  SELECT ["user_id: 1342", "foo: bar", "baz: xxx"] arr UNION ALL
  SELECT ["user_id: 4312", "foo: ds", "baz: dss"] UNION ALL
  SELECT ["user_id: 512", "foo: fsf", "baz: aas"]
)
SELECT * EXCEPT(rn) FROM (
  SELECT SPLIT(kv, ':')[OFFSET(0)] k,
         TRIM(SPLIT(kv, ':')[OFFSET(1)]) v,
         rn
    FROM (SELECT *, ROW_NUMBER() OVER () rn FROM sample_table), UNNEST(arr) kv
) PIVOT (ANY_VALUE(v) FOR k IN ('user_id', 'foo', 'baz'));
Jaytiger0
Related