-
Understanding SQL Server parsing of GA4 BigQuery RECORD columns
I’m using a Python script to pull daily GA4 event files from BigQuery and save them to a SQL Server DW. It’s mostly easy because the columns are Strings or Integers. But there are 11 fields of type RECORD which are kind of a headache. According to Google, RECORDS are storable as STRUCT type in GoogleSQL and sometimes show up as arrays. Have a look at these samples of ‘event_params’ data, which is an array of STRUCT types, and ‘device’ data, which is a STRUCT. Can anyone show me a SQL function that can parse STRUCTS or arrays of STRUCTS? Is there a way to cast this data as JSON so I can use the built-in JSON functions to query it?
-
2 Replies
-
Alright! You can use the
UNNESTfunction in BigQuery, which allows you to flatten array data into a table. Here’s an example using the ‘event_params’:`
SELECT
EP.key AS event_param_key,
COUNT(*) AS occurrences
FROM
— Replace the table name with your table
bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*,
UNNEST(event_params) AS EP
WHERE
— Replace with your date range
_TABLE_SUFFIX BETWEEN ‘20201201’ AND ‘20201202’
GROUP BY
event_param_key
ORDER BY
event_param_key ASC;
`In this SQL query, each record in the ‘event_params’ array is transformed into a row in the ‘EP’ table, which you can then query as usual.
For more details, have a look at [this article](https://medium.com/firebase-developers/using-the-unnest-function-in-bigquery-to-analyze-event-parameters-in-analytics-fb828f890b42) which explains how to use the
UNNESTfunction in depth when analyzing event parameters in Analytics. -
In BigQuery, you can use functions like UNNEST to handle arrays of STRUCTs and extract their individual elements. However, if you’d prefer to convert the STRUCTs data into JSON format, BigQuery also allows this by using the TO_JSON_STRING function. Then, you can easily parse and analyze your data using BigQuery’s built-in JSON functions.
Log in to reply.