How to simplify and expand BigQuery subfiltering event_params on firebase database

user_3633687 0
Viewed 358

I'm trying to make a BigQuery query to extract data from the firebase db sub-table event_params. The tables events_* have a list of events in which the field event_params is a struct that contains a table in the form of key, int_value and other fields not used in this case.

For example, 1 single event may have a list of event_params like this:

key    int_value
level  1
time   35
kills  10

I'd like to filter the list of events by the level but select the time from that event. The solution I got is this:

select user_pseudo_id, x.value.int_value as time
from (

  select user_pseudo_id, event_params
  from `whatever.events_*`, unnest(event_params) as y
  where event_name = 'level_complete' and y.key = 'level' and y.value.int_value = 1

), unnest(event_params) as x
where x.key = 'time'

It works for this case but I have a few questions on how to improve and make this grow:

  • Is there a way of simplifying this in one single query?
  • What if I want to get the time field but also the kills field? (The only solution I can think of is duplicating the query and joining both results, but what if I want to extract a lot of fields?).
  • Can this be simplified by creating something like a function that returns a resulting table like Filter('level_complete', 'level', 1) ?

Thanks in advance!

google-bigquery sql
2 Answers

This one is less resource intensive because it does not perform aggregation:

select *
from (
  select 
    user_pseudo_id,
    event_name,
    (select int_value from unnest(event_params) where key = 'level') as level,
    (select int_value from unnest(event_params) where key = 'time') as time,
    (select int_value from unnest(event_params) where key = 'kills') as kills
  from `whatever.events_*`
  where event_name = 'level_complete'
)
where level = 1
user_134735250

Pivot the data:

# start with some sample data
with data as (
select 1 as user_pseudo_id, 'level_complete' as event_name, [struct('level' as key, 1 as int_value), struct('time' as key, 35 as int_value), struct('kills' as key, 10 as int_value)] as event_params
UNION ALL
select 2 as user_pseudo_id, 'level_complete' as event_name, [struct('level' as key, 4 as int_value), struct('time' as key, 10 as int_value), struct('kills' as key, 1 as int_value)] as event_params
UNION ALL
select 3 as user_pseudo_id, 'level_complete' as event_name, [struct('level' as key, 6 as int_value), struct('time' as key, 30 as int_value), struct('kills' as key, 2 as int_value)] as event_params
UNION ALL
select 3 as user_pseudo_id, 'other_type' as event_name, [struct('bad' as key, 5 as int_value), struct('time' as key, 40 as int_value), struct('kills' as key, 3 as int_value)] as event_params
)
# This is the "code" part
select user_pseudo_id, event_name,
  max(if(params.key = 'level', int_value, null)) level,
  max(if(params.key = 'time', int_value, null)) time,
  max(if(params.key = 'kills', int_value, null)) kills,
  array_agg(distinct if(params.key in ('level','time','kills'), null, params.key) ignore nulls) as unexpected_keys
from data
CROSS JOIN unnest(event_params) as params
group by user_pseudo_id, event_name

Or if you prefer to create transformed table in this format, try fhoffa.x.pivot()

I use this technique all of the time with a python program that queries all of the unique values of key and then creates a VIEW with the appropriate dynamic columns. If your values are static then hand-written SQL simpler.

user_73595020
Related