CODEWITHSUNDEEP
google-bigquery
Terry Cho
Terry Cho

Reputation: 618

bigquery nested & repeated field query

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

Upvotes: 2

Views: 934

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

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.

Upvotes: 3

Related Questions