How to select multiple custom Firebase event parameters in BigQuery?

Question

I exported Firebase events to BigQuery and now I'm trying to select two parameters from a certain event. Here is the query for selecting one parameter:

select event_dim.params.value.int_value as level_id
from [com_company_appname_ANDROID.app_events_20161210]
where event_dim.name = "level_replays_until_first_victory" and  event_dim.params.key = "level_id"

Both parameters are int values, name of the first parameter is level_id, and the second parameter is count. What I would like is to show is level_id in first column and count in second column.

Solution 1

Below will work with BigQuery Standard SQL

SELECT 
  (SELECT params.value.int_value FROM x.params 
                                 WHERE params.key = 'level_id') AS level_id,
  (SELECT params.value.int_value FROM x.params 
                                 WHERE params.key = 'count') AS count
FROM `com_company_appname_ANDROID.app_events_20161210`, UNNEST(event_dim) AS x
WHERE x.name  = 'level_replays_until_first_victory'

See also Migrating from legacy SQL in case if you are stuck with Legacy SQL

Solution 2

I love the previous solution! Here is an alternative solution for the same problem I came up with. I'd welcome comments on which solution is more efficient/cheaper and why.

SELECT event_param1.value.int_value AS level_id, 
event_param2.value.int_value AS count
FROM `com_company_appname_ANDROID.app_events_20161210`,
UNNEST(event_dim) event,
UNNEST(event.params) as event_param1,
UNNEST(event.params) as event_param2
WHERE event.name = 'level_replays_until_first_victory'
AND event_param1.key = 'level_id'
AND event_param2.key = 'count'

Solution 3

Another solution I find quite handy is to use User Defined Functions to analyze user properties and event parameters

#Standard-SQL

#UDF for event parameters
CREATE TEMP FUNCTION paramValueByKey(k STRING, params ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64 >>>) AS (
  (SELECT x.value FROM UNNEST(params) x WHERE x.key=k)
);

#UDF for user properties
CREATE TEMP FUNCTION propertyValueByKey(k STRING, properties ARRAY<STRUCT<key STRING, value STRUCT<value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64>, set_timestamp_usec INT64, index INT64 > >>) AS (
  (SELECT x.value.value FROM UNNEST(properties) x WHERE x.key=k)
);

#Query the sample dataset, unnesting the events and turn 'api_version', 'round' and 'type_of_game' into columns 
SELECT 
  user_dim.user_id,
  event.name,
  propertyValueByKey('api_version', user_dim.user_properties).string_value AS api_version,
 paramValueByKey('round', event.params).int_value as round,
 paramValueByKey('type_of_game', event.params).string_value as type_of_game
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`,
UNNEST(event_dim) as event
WHERE event.name = 'round_completed'
LIMIT 10;

Comments

[Luka Maške]

Now lets say I'd like to only show the rows, where the "count = 5", how would you formulate that in this case?

[Mikhail Berlyant]

there are many ways. to make my reply fit into comment - just add to the very end of query next highlighted statement AND (SELECT params.value.int_value FROM x.params WHERE params.key = 'count') = 5. if you have more questions - please do not use comments for asking - rather please post new question and we will be happy to help

[Admin]

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.