How to map each parameter in firebase analytics sql to a separate column?

Question

We use firebase analytics and bigQuery to run sql queries on collected data. This is turning out to be complex as some fields like event_params are repeated records. I want to map each of these repeated fields to separate column.

I want to write queries in the above dataset like finding the difference between minIso and maxIso. How can I define a UDF or a view which can return me the table in the column schema?

Answer 1

I want to map each of these repeated fields to separate column.

Going direction of pivoting parameters into columns conceptually doable but (in my strong opinion) is a “dead end” in most practical cases
There are many posts here on SO showing how to pivot/transpose rows to columns and the patterns are 1) you just hardcode all possible keys in your query )and obviously no-one likes this) or 2) you create utility query that extracts all keys for you and contracts needed query for you which then you need to execute – so either you do it manually in two steps or you using client of your choice to script those to steps to run in automated way
As I mentioned – there are plenty example of such here on SO

I want to write queries in the above dataset like finding the difference between minIso and maxIso

If all you need is to do some math with few parameters in the record – see below example

Dummy Example: for each app_instance_idtween find diff between coins_awarded and xp_awarded

#standardSQL
SELECT user_dim.app_info.app_instance_id,  ARRAY(
  SELECT AS STRUCT name,
    (SELECT value.int_value FROM UNNEST(dim.params) param WHERE key = 'coins_awarded') -
    (SELECT value.int_value FROM UNNEST(dim.params) param WHERE key = 'xp_awarded') diff_awarded
  FROM UNNEST(event_dim) dim
  WHERE dim.name = 'round_completed'  
  ) AS event_dim
FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160607`
WHERE 'round_completed' IN (SELECT name FROM UNNEST(event_dim))  

with result as

Row     app_instance_id                     event_dim.name      event_dim.diff_awarded   
1       02B6879DF2639C9E2244AD0783924CFC    round_completed     226  
2       02B6879DF2639C9E2244AD0783924CFC    round_completed     171  
3       0DE9DCDF2C407377AE3E779FB05864E7    round_completed     25   
...

Dummy Example: leave whole user_dim intact but replace event_dim with just calculated values

#standardSQL
SELECT * REPLACE(ARRAY(
  SELECT AS STRUCT name,
    (SELECT value.int_value FROM UNNEST(dim.params) param WHERE key = 'coins_awarded') -
    (SELECT value.int_value FROM UNNEST(dim.params) param WHERE key = 'xp_awarded') diff_awarded
  FROM UNNEST(event_dim) dim
  WHERE dim.name = 'round_completed'  
  ) AS event_dim)
FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160607`
WHERE 'round_completed' IN (SELECT name FROM UNNEST(event_dim)) 

This is turning out to be complex as some fields like event_params are repeated records. I want to map each of these repeated fields to separate column.

Hope, from above examples, you can see how really simple it is to deal with repeated fields. I do really recommend you to learn / practice work with arrays to gain long term benefits rather than looking for what [wrongly] looks like shortcut