CODEWITHSUNDEEP
firebasegoogle-bigqueryfirebase-analytics
AEDWIP
AEDWIP

Reputation: 948

newbie bigquery how to select multiple parameters from an firebase event (schema record, repeated)

I am trying to select to parameters from firebase events named "SCI_ERROR"

I am new new Firebase and BigQuery. I watched the Firebase BigQuery video tutorial. I think maybe it is a little out of date? I tried using several posted solutions I found on stackOverflow. I could never run them because of errors.

I assume best practice is use the 'standard query' syntax.

I think where I am running into trouble is that all the examples I have seen suggest there is a table 'event_dims' . when I look at the schema I see event_name and event_params

Here is my sql statement

SELECT 
(SELECT value.string_value FROM x
                             WHERE key = 'TITLE') AS level_id,
(SELECT value.string_value FROM x
                             WHERE key = 'url') AS url
FROM `sci.analytics_179015875.events_20180725` ,
  UNNEST(event_params) as x
WHERE event_name = 'SCI_ERROR'

Here is the error

Error: Table name "x" cannot be resolved: dataset name is missing.

Thanks in advance

Andy

Upvotes: 6

Views: 2227

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'TITLE') AS level_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'url') AS url
FROM `sci.analytics_179015875.events_20180725`
WHERE event_name = 'SCI_ERROR'

Upvotes: 10

Related Questions