[et_pb_section fb_built=”1″ _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
UNNEST in BigQuery is a very useful function that allows you to flatten nested and repeated data structures for easier analysis. When dealing with complex hierarchical data like arrays, UNNEST comes to the rescue.<\/p>\n
Unlike traditional tabular data, these arrays contain nested and repeated data, which can make aggregation and joins more difficult. When this happens, UNNEST allows you to expand nested or repeated data into separate rows so you can analyze the unnested elements much more easily.<\/p>\n
In this article, we’ll explore the UNNEST function in BigQuery to see how it can simplify nested data analysis.\u00a0<\/p>\n
[\/et_pb_text][et_pb_text module_id=”nested” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
When analyzing large and complex datasets in BigQuery, it’s common to encounter nested and repeated fields. These nested data structures allow you to store multiple values in a single field in BigQuery, making it easier to manipulate multiple values at once.<\/p>\n
When you look at the table below, you will notice that the column with the “event parameters” contains subcolumns with multiple values, which are all part of the same row.\u00a0<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/Unnest-BigQuery-1.png” alt=”Unnest BigQuery 1″ title_text=”Unnest BigQuery 1″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
If you examine the table schema, you’ll see this column’s data type isn’t an integer, string, float, or boolean. Rather, it’s defined as a record since it contains subcolumns.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/Unnest-BigQuery-2.png” alt=”Unnest BigQuery 2″ title_text=”Unnest BigQuery 2″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, the “event_params” field has subcolumns (“key” and “value”) with multiple values inside. Notice that BigQuery supports nested data with subcolumns within subcolumns. In this table, “value” contains multiple subcolumns.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/Unnest-BigQuery-3-1.png” alt=”Unnest BigQuery 3″ title_text=”Unnest BigQuery 3″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”how” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The UNNEST function in BigQuery is used to flatten nested or repeated data structures into separate rows. What it does is take as input a column with a nested data type like an ARRAY, and expand the nested or repeated elements into multiple rows. As a result, it will flatten the data into a tabular structure.<\/p>\n
After UNNEST transforms the nested or repeated data into a flat table, you can query columns directly and analyze its individual elements.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-unnest.png” title_text=”BigQuery unnest” align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”use” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In order to flatten nested data structures in BigQuery, we’ll use the UNNEST function. Here’s the UNNEST syntax:<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT column1, column2, \u2026<\/p>\n
FROM table_name, UNNEST(column) AS alias\u00a0<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
We’ll use the UNNEST function to break the “event_params” column down into separated rows.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT ga.event_name, params.key, params.value<\/p>\n
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131` AS ga,<\/p>\n
UNNEST(event_params) AS params<\/p>\n
LIMIT 1000;<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Let’s take a closer look at the query.<\/p>\n
SELECT ga.event_name, params.key, params.value<\/b><\/p>\n
First, I’ve specified the columns I want BigQuery to return: event_name, params.key, and params.value. “Params” is the alias I’ll assign when I write the UNNEST function.<\/p>\n
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131` AS ga,<\/b><\/p>\n
This is the table I want to query. I’ve used an AS statement to assign an alias to the table: “ga”.<\/p>\n
UNNEST(event_params) AS params<\/b><\/p>\n
The UNNEST function will expand the nested data into separate rows.<\/p>\n
LIMIT 1000;<\/b><\/p>\n
This clause limits the number of rows returned to 1000.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/Unnest-BigQuery-4.png” alt=”Unnest BigQuery 4″ title_text=”Unnest BigQuery 4″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
BigQuery has expanded the data into separate rows, flattening the nested data.<\/p>\n