BigQuery select multiple key values

Question

With a custom event in Firebase exported to BigQuery, multiple key-value params can exist within it. I can't seem to figure out how to select more than just one of these using "standard SQL".

Answer 1

Let's say that you wanted to select the string_value that corresponds with firebase_event_origin and the int_value associated with firebase_screen_id for all control_reading events. You could express the query as:

#standardSQL
SELECT
  (SELECT param.value.string_value
   FROM UNNEST(event_dim.params) AS param
   WHERE param.key = 'firebase_event_origin') AS firebase_event_origin,
  (SELECT param.value.int_value
   FROM UNNEST(event_dim.params) AS param
   WHERE param.key = 'firebase_screen_id') AS firebase_screen_id
FROM `your_dataset.your_table_*`
CROSS JOIN UNNEST(event_dim) AS event_dim
WHERE _TABLE_SUFFIX BETWEEN '20170501' AND '20170503' AND
  event_dim.name = 'control_reading';