Unable to extract key-value pairs from BigQuery table which has arrays and structs

Question

I have integrated Firebase analytics data with google BigQuery and everyday a new table gets created with the DATE stamp.

one sample table is "projectID.com_dev_sambhav_ANDROID.app_events_20170821"

One sample table is shown below sample table

My requirement is to get result in the below desired format for event_dim.name="notification_received" desired output

To get this desired output I execute the below query(standardSQL)

SELECT event.name
 (SELECT param.value.string_value FROM UNNEST(event_dim.params) AS param WHERE param.key="notification_title") as notification_title,
 (SELECT param.value.string_value FROM UNNEST(event_dim.params) AS param WHERE param.key="item_id") as item_id 
FROM `projectID.com_dev_sambhav_ANDROID.app_events_20*`, UNNEST(event_dim) as event  
WHERE event.name = "notification_received"

But I got this error

Error: Each function argument is an expression, not a query. To use a query as an expression, the query must be wrapped with additional parentheses to make it a scalar subquery expression.

can anybody help me get out of this problem

Answer 1

The problem is a comma and an alias. This query works:

#standardSQL
WITH `projectID.com_dev_sambhav_ANDROID.app_events_2017` AS(
  SELECT ARRAY< STRUCT<date STRING, name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('20170814' AS date, 'notification_received' AS name, [STRUCT('notification_title' AS key, STRUCT('Amazing Offers two' AS string_value) AS value ), 
                                                                                                                STRUCT('firebase_screen_class' AS key, STRUCT('RetailerHomeActivity' AS string_value) AS value),
                                                                                                                STRUCT('notification_id' AS key, STRUCT('12345' AS string_value) AS value),
                                                                                                                STRUCT('firebase_screen_id' AS key, STRUCT('app' AS string_value) AS value),
                                                                                                                STRUCT('item_id' AS key, STRUCT('DEMO-02' AS string_value) AS value),
                                                                                                                STRUCT('firebase_screen' AS key, STRUCT('My Order' AS string_value) AS value)] AS params)] event_dim
)

SELECT
 event.name,
 (SELECT param.value.string_value FROM UNNEST(event.params) AS param WHERE param.key="notification_title") as notification_title,
 (SELECT param.value.string_value FROM UNNEST(event.params) AS param WHERE param.key="item_id") as item_id 
FROM `projectID.com_dev_sambhav_ANDROID.app_events_20*`, UNNEST(event_dim) as event  
WHERE event.name = "notification_received"

If you UNNEST the field event_dim and call it event, then you should use this alias as reference in your query.

As a complement, here's another way of solving your problem as well (it's just another possibility so you have more techniques in your belt when working with BigQuery):

#standardSQL
SELECT
  (SELECT date FROM UNNEST(event_dim)) date,
  (SELECT params.value.string_value FROM UNNEST(event_dim) event, UNNEST(event.params) params WHERE event.name = 'notification_received' AND params.key = 'notification_title') AS notification_title,
  (SELECT params.value.string_value FROM UNNEST(event_dim) event, UNNEST(event.params) params WHERE event.name = 'notification_received' AND params.key = 'item_id') AS item_id
FROM `projectID.com_dev_sambhav_ANDROID.app_events_2017`
WHERE EXISTS(SELECT 1 FROM UNNEST(event_dim) WHERE name = 'notification_received')

When processing up to terabytes you may find this query still perform quite well.