How to get COUNT with UNNEST function in Google BigQuery?

Question

I need to get count of events which has specific parameter in it. Let's say, I have param event_notification_received with params (type, title, code_id). And in param code_id - I have unique name of advertisement. And I need to count how much events where received with such parameter. I am using UNNEST function, to get access to params of event. But it gives too much of results after execution, I think it's because of UNNEST function. How can I count correctly events? Thanks.

Here is my standard SQL query:

#standardSQL
SELECT event_date, event_timestamp, event_name, user_id, app_info.version, 
geo.country, geo.region, geo.city,  
my_event_params, 
user_prop,
platform
FROM 
`myProject.analytics_199660162.events_201807*`,
UNNEST(event_params) as my_event_params,
UNNEST(user_properties) as user_prop

WHERE
_TABLE_SUFFIX BETWEEN '24' AND '30' AND
event_name = "event_notification_received"

AND
my_event_params.value.string_value = "my_adverticement_name"

AND
platform = "ANDROID"

ORDER BY event_timestamp DESC

it is not clear what exactly is expected in output rather than count! is it just single count? — Mikhail Berlyant, Aug 02 '18 at 17:27

score 3 · Accepted Answer · answered Aug 02 '18 at 11:02

Is this what you want?

SELECT . . .,
       (SELECT COUNT(*)
        FROM UNNEST(event_params) as my_event_params
        WHERE my_event_params.value.string_value = 'my_adverticement_name'
       ) as event_count
FROM `myProject.analytics_199660162.events_201807*`,
      UNNEST(user_properties) as user_prop
WHERE _TABLE_SUFFIX BETWEEN '24' AND '30' AND
      event_name = 'event_notification_received' AND
      platform = 'ANDROID'
ORDER BY event_timestamp DESC;

score 1 · Answer 2 · answered Aug 02 '18 at 08:58

1

If you UNNEST() and CROSS JOIN more than two columns at the FROM level, you'll get duplicated rows - yup.

Instead UNNEST() at the SELECT level, just to extract and COUNT the values you are looking for:

SELECT COUNT(DISTINCT (
    SELECT value.string_value FROM UNNEST(user_properties) WHERE key='powers')
  ) AS distinct_powers
FROM `firebase-sample-for-bigquery.analytics_bingo_sample.events_20160607`

answered Aug 02 '18 at 08:58

Felipe Hoffa

54,922
16
151
325

If you make an example over a public dataset, I can test it too. – Felipe Hoffa Aug 02 '18 at 16:19

How to get COUNT with UNNEST function in Google BigQuery?

2 Answers2