My team decided to store Firebase events in BigQuery using repeated record schema:
The table will look something like this:
event_name | event_params.key | event_params.value.string_value
--------------------------------------------------------------------
element_clicked | element_id | button-1
| result | ok
--------------------------------------------------------------------
element_clicked | element_id | image-23
--------------------------------------------------------------------
screen_openend | screen_id | launch_screen
Is there a way that will allow me to transform repeated records into the columns, so the new table looks like this:
event_name | element_id | result | screen_id
-----------------------------------------------------
element_clicked | button-1 | ok | NULL
-----------------------------------------------------
screen_opened | NULL | NULL | launch_screen
-----------------------------------------------------
element_clicked | image-34 | NULL | NULL
Alternatively: is there any other easy way that will allow me to query e.g. "events with name element_clicked and element_id = button-1 or events with name screen_opened and screen_id = launch_screen"?
I started to experiment with UNNEST function, but I don't think it will behave correctly when event_params list differs from row to row:
SELECT
event_name,
ELEMENT_ID.value.string_value AS element_id,
RESULT.value.string_value AS result,
SCREEN_ID.value.string_value AS screen_id
FROM my_table, UNNEST(event_params) AS ELEMENT_ID, UNNEST(event_params) AS RESULT, UNNEST(event_params) AS SCREEN_ID
WHEN ELEMENT_ID.key = 'element_id' AND RESULT.key = 'result' AND SCREEN_ID.key = 'screen_id'
