Google Firebase and Big Query : SQL Array to Columns

Question

I am attempting to split a string ("item1,item2,item3") that is an event param (key = "to_split", value.string_value = "item1,item2,item3") into multiple columns in Google BigQuery. Currently, I have:

SELECT
    event_name,
    PARSE_DATE("%Y%m%d", event_date) as date, 
    TIMESTAMP_MICROS(event_timestamp) as timestamp,
   (
     SELECT split(params.value.string_value, ',')
     FROM UNNEST(event_params) as params
     WHERE key = 'event_param-key_to_filter'
   ),
FROM `firebase.analytics.table`

Which will give results like:

event_name	date	timestamp	f0_
Example Event	2021-06-15	2021-06-15 10:08:41.634055 UTC	Item\nItem\nItem\nItem

(Formatting doesn't like it, but the split turns the items into an array)

I am trying to get results such that I have:

event_name	date	timestamp	item0	item1	item2	item...
Example Event	2021-06-15	2021-06-15 10:08:41.634055 UTC	Item	Item	Item	etc...

Is there a good way to split this item array into columns along the same row in SQL for BigQuery? I've been stuck on this for quite some time.

Best

Answer 1

You can use array references:

SELECT t.*,
       items[SAFE_ORDINAL(1)] as item_1,
       items[SAFE_ORDINAL(2)] as item_2,
       items[SAFE_ORDINAL(3)] as item_3,
       items[SAFE_ORDINAL(4)] as item_4
FROM (SELECT event_name, PARSE_DATE("%Y%m%d", event_date) as date, 
             TIMESTAMP_MICROS(event_timestamp) as timestamp,
             (SELECT split(params.value.string_value, ',') 
              FROM UNNEST(event_params) as params
              WHERE key = 'event_param-key_to_filter'
             ) items
      FROM `firebase.analytics.table` t
     ) t