bigquery nested & repeated field query

Question

I'm making some example for Firebase analytics query by using bigquery. I have a record like this.

event_dim:[

  “name”:”MOVE”,

  “params”:[

     {

         “key”:”npc_id”,

         “value”:{

              "string_value": “11”,

              "int_value": null,

              "float_value": null,

              "double_value": null

            }

     },

     {

         “key”:”posX”,

         “value”:{

              "string_value": null,

              "int_value": null,

              "float_value": null,

              "double_value": “10.0”

            }

     },

     {

         “key”:”posY”,

         “value”:{

              "string_value": null,

              "int_value": null,

              "float_value": null,

              "double_value": “12.0”

            }

     },

]

How can i query this data to result like this?

name | npc_id | posX | posY

MOVE| 11 | 10.0 | 12.0

Can you see if this past question helps first? stackoverflow.com/questions/40406286/… — Elliott Brossard

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2016-11-04T22:33:14

Working query:

#StandardSQL
SELECT * FROM (
  SELECT 
    e.name, 
    (SELECT value.string_value FROM UNNEST(e.params) WHERE key = "item_name") AS item_name,
    (SELECT value.int_value FROM UNNEST(e.params) WHERE key = "value") AS value
  FROM 
    `firebase-analytics-sample-data.ios_dataset.app_events_20160601`,
    UNNEST(event_dim) as e
)
WHERE item_name IS NOT null

The secret is to UNNEST the nested event records, and then UNNEST the nested params, looking for the one with the right key.

bigquery nested & repeated field query

1 Answers