Unnest all nested rows (GA4/BigQuery)

Question

I need to unnest all event_params for all events coming from GA4 that are stored in nested table in BigQuery.

What I need to achieve is to be able to extract all events with all of their event_params without specifying the names for each of them and their data types, simply fully unnest all records.

Writing an unnest query for each event_param will not work as new ones will constantly be added.

Answer 1

You will have to use some sort of script in order to dynamically extract all available params into columns. Either generate a list of available params in an SQL Statement Script, and then iterate over that list to generate the columns, or use dbt scripts to perform the same.

See examples here:

Explanations about SQL Procedural Language in BigQuery

dbtCore Jinja & Loops

OR - you can use the existing ga4 dbt package by Velir, available here:

ga4 dbt package by Velir