BigQuery Quick Tips – How to Unnest Nested Fields

BigQuery can store data in nested fields. It uses to denormalize data storage and increase query performance.

For a first time user nested fields could be a big issue despite the advantages above.

First of all it is a bit hard to imagine the data. It is not just a simple table, but some fields have tables inside the field. Ok it sound chaotic but is not, just you have to reimagine the table.

You can find the GA4 sample bigquery data set here . You can use this table to learn the basics. The data comes from the Google Merchandise Store.

So, how a nested field looks like?

You can see here the pageview event and it’s parameters, eg session id, page location and title. All of these parameters belongs to this single page view event. Instead of add the event name to every single line and increase the size of the table they just add a “table” to the page view event

Ok, I know it looks weird and a bit harder to query it, but it not as bad as you think.

If you would like to write a query to get the page_title parameter, you can’t do in this way because you get an error:

You can’t access to this field in this way. It is nested under the event_params field. So, first you have to unnest this field.

To unnest a nested field you have to use the UNNEST operator after the FROM. You just simply have to declare what field/array you would like to unnest and name it:

SELECT e_par FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`,
UNNEST (event_params) as e_par

Here we unnested the event_params as e_par, so now you can use the “inner” fields as separate fields.