BigQuery - UNNEST where event_params.key matches a certain value

1
votes

I'm trying to pull a distinct count of users WHERE the traffic medium = 'referral' On running the below query, I get this error: Syntax error: Unexpected keyword UNNEST at [4:1] I'm trying to UNNEST the event_params field so that I flatten the table. I'm also sharing a sample row of the data which has the event_params.key / value pairs. Thanks.

SELECT 
COUNT (DISTINCT(user_pseudo_id)) AS total_users
FROM `project-table`
UNNEST (event_params) AS event_params
WHERE event_name = 'page_view' 
AND event_params.medium='referral'

enter image description here

1
google-bigquery

1 Answers

2
votes

Below is for BigQuery Standard SQL

#standardSQL
SELECT COUNT (DISTINCT(user_pseudo_id)) AS total_users
FROM `project.dataset.table`,
UNNEST (event_params) AS event_param
WHERE event_name = 'page_view' 
AND event_param.key = 'medium' 
AND event_param.value.string_value = 'referral'