Pivot Repeated fields in BigQuery

2
votes

My Schema looks something like this:

userid:string
timestamp:integer
params:nested/repeated field with 2 fields
  - name:string (possible values: "a", "b","c")
  - value:string

I want my query to return the following:

userid, timestamp, a, b, c
123, 1447799796, foo, bar, xyz
233, 1447799900, bob, xxx, yyy
:
:

What's the easiest way to do this?

2
google-bigquery

2 Answers

2
votes

when possible values are known in advance and there are not that many of them to write manually SQL - you can use below:

SELECT 
  userid,
  ts,
  MAX(IF(params.name = "a", params.value, NULL)) WITHIN RECORD a,
  MAX(IF(params.name = "b", params.value, NULL)) WITHIN RECORD b,
  MAX(IF(params.name = "c", params.value, NULL)) WITHIN RECORD c
FROM yourTable

If possible values are "unknown" in advance and/or dynamic from run to run, you can use below helper SQL to generate above type of SQL. 

SELECT 'select userid, ts, ' + 
   GROUP_CONCAT_UNQUOTED(
      'max(if(params.name = "' + STRING(params.name) + '", params.value, null)) 
       WITHIN RECORD as [' + STRING(params.name) + ']'
   ) 
   + ' from yourTable '
FROM (SELECT params.name FROM yourTable GROUP BY params.name)
1
votes

Something along these lines:

SELECT 
  userid,
  timestamp,
  FIRST(name == "a", value, NULL) WITHIN RECORD a,
  FIRST(name == "b", value, NULL) WITHIN RECORD b,
  FIRST(name == "c", value, NULL) WITHIN RECORD c,
FROM t