Pivot Duplicate Fields in BigQuery

Question

My diagram 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 is the easiest way to do this?

Answer 1

when the possible values ​​are known in advance, and many of them cannot 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 the possible values ​​are "unknown" in advance and / or dynamic from start to start, you can use the auxiliary SQL below to generate the upstream SQL type.

 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) 

Answer 2

Something like that:

 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