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

Question

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

Answer 1

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'