Querying specific values within a "RECORD"

Question

I've been trying recently to query our Firebase Analytics data directly since the Firebase Dashboard only has some very basic data viewing capability.

It seems we can query the data directly in BigQuery, however it seems the type of database BigQuery uses is column based storage and has different data types than traditional databases like MySQL.

So I end up with the following task:

I have tried to read some documentation on BigQuery's official documentation, such as here:

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

But it seems it's about arrays, I don't really understand the documentation and in the BigQuery dashboard it says these columns are not actually an ARRAY, they are of type "RECORD":

.. for which I did not find much information online either.

So try to still query this since I don't really know how to go about it, I just tried something that probably will look stupid to you all hahaha:

SELECT *
FROM `diningcity-2ad82.analytics_171798853.events_20201222`
WHERE event_params.string_value.screen_name = "Deal details"
LIMIT 1000;

So I try to use the "." as a way to tell BigQuery "First go to event_params, then inside to string_value then to screen_name, to check final value".

But BigQuery doesn't like it:

Cannot access field string_value on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>> at [3:20]

So can someone help me find out what I'm doing wrong and what is the proper way to do this?

For reference I also tried going through this tutorial but also didn't really understand 😅😭:

https://www.simba.com/products/BigQuery/doc/JDBC_InstallGuide/content/jdbc/bq/features/records.htm

Answer 1

What the BigQuery table format is saying is:

• event_params is an array of structs -- this is what "repeated" means.
• value is a struct inside that struct.
• string_value is a string field in that array.

I don't know why BigQuery uses "record" in the table description but "struct" everywhere else in the documentation.

To access arrays, use unnest. One method is in the FROM clause. As you have phrased the query, this would be:

SELECT *
FROM `diningcity-2ad82.analytics_171798853.events_20201222` e CROSS JOIN
     UNNEST(e.event_params) ep 
WHERE v.screen_name = 'Deal details'
LIMIT 1000;

The only issue is that screen_name is probably the "key" value and not a column/field name. So, you really want:

SELECT *
FROM `diningcity-2ad82.analytics_171798853.events_20201222` e CROSS JOIN
     UNNEST(e.event_params) ep 
WHERE ep.key = 'screen_name' AND
      ep.value.string_value = 'Deal details'
LIMIT 1000;

If you don't want all the parameters in the result set, you can use:

SELECT * EXCEPT (event_params)

You can also do this in a subquery:

SELECT *
FROM `diningcity-2ad82.analytics_171798853.events_20201222` e         
WHERE EXISTS (SELECT 1
              FROM UNNEST(e.event_params) ep 
              WHERE ep.key = 'screen_name' AND
                    ep.value.string_value = 'Deal details'
LIMIT 1000;

This is handy if you don't want to multiple the number of rows when a row could have more than one match.