Select multiple event parameters in one row for Firebase events stored in Google BigQuery

Question

I am trying to execute a very simple query for Firebase events stored in Google BigQuery, but I cannot find a way to do this.

In an Android application, I register an event as follows:

Bundle params = new Bundle(); params.putInt("productID", productId); params.putInt(FirebaseAnalytics.Param.VALUE, value); firebaseAnalytics.logEvent("productEvent", params); 

So, in BigQuery, I have something like this:

  ___________________ _______________________ ____________________________ 
 |  event_dim.name |  event_dim.params.key |  event_dim.params.int_value | 
 | ___________________ | _______________________ | ____________________________ |
 |  productEvent |  productID |  25 | 
 |  | _______________________ | ____________________________ | 
 |  |  value |  1253 |
 | ___________________ | _______________________ | ____________________________ | 

When I get data from this table, I get two rows:

  ___________________ _______________________ ____________________________
 | event_dim.name |  event_dim.params.key |  event_dim.params.int_value |
 | ___________________ | _______________________ | ____________________________ |
 |  productEvent |  productID |  25 |
 |  productEvent |  value |  12353 | 

But I really need a SELECT clause from this table to get the following data:

  ___________________ _____________ _________
 |  name |  productID |  value |
 | ___________________ | _____________ | _________ |
 |  productEvent |  25 |  12353 | 

Any idea or suggestion?

Answer 1

You can rotate values ​​to columns like this

 SELECT event_dim.name as name, MAX(IF(event_dim.params.key = "productID", event_dim.params.int_value, NULL)) WITHIN RECORD productID, MAX(IF(event_dim.params.key = "value", event_dim.params.int_value, NULL)) WITHIN RECORD value, FROM [events] 

If you want to generate this command using SQL, see this solution: Pivot Duplicate fields in BigQuery

Answer 2

Using standard SQL (uncheck "Use legacy SQL" in the "Display Options" section of the user interface), you can express the query as:

 SELECT event_dim.name as name, (SELECT value.int_value FROM UNNEST(event_dim.params) WHERE key = "productID") AS productID, (SELECT value.int_value FROM UNNEST(event_dim.params) WHERE key = "value") AS value FROM `dataset.mytable` AS t, t.event_dim AS event_dim; 

Edit: Updated example to include int_value as part of value based on the comment below. The following is a self-sufficient example that also demonstrates the approach:

 WITH T AS ( SELECT ARRAY_AGG(event_dim) AS event_dim FROM ( SELECT STRUCT( "foo" AS name, ARRAY<STRUCT<key STRING, value STRUCT<int_value INT64, string_value STRING>>>[ ("productID", (10, NULL)), ("value", (5, NULL)) ] AS params) AS event_dim UNION ALL SELECT STRUCT( "bar" AS name, ARRAY<STRUCT<key STRING, value STRUCT<int_value INT64, string_value STRING>>>[ ("productID", (13, NULL)), ("value", (42, NULL)) ] AS params) AS event_dim ) ) SELECT event_dim.name as name, (SELECT value.int_value FROM UNNEST(event_dim.params) WHERE key = "productID") AS productID, (SELECT value.int_value FROM UNNEST(event_dim.params) WHERE key = "value") AS value FROM T AS t, t.event_dim AS event_dim;