You have one struct that is repeated. So, I think you want:
SELECT origin,
s.destination,
s.visitors
FROM dataset.table t CROSS JOIN
UNNEST(t.struct) s;
EDIT:
I see, you have a struct of two arrays. You can do:
SELECT origin, d.destination, v.visitors
FROM dataset.table t CROSS JOIN
UNNEST(struct.destination) s WITH OFFSET nd LEFT JOIN
UNNEST(struct.visitors) v WITH OFFSET nv
ON nd = nv
Top answer 1 of 3
17
You have one struct that is repeated. So, I think you want:
SELECT origin,
s.destination,
s.visitors
FROM dataset.table t CROSS JOIN
UNNEST(t.struct) s;
EDIT:
I see, you have a struct of two arrays. You can do:
SELECT origin, d.destination, v.visitors
FROM dataset.table t CROSS JOIN
UNNEST(struct.destination) s WITH OFFSET nd LEFT JOIN
UNNEST(struct.visitors) v WITH OFFSET nv
ON nd = nv
2 of 3
0
Difficult to test by not having the underlying data to test on, so I created my own query with your dataset. As far as I can tell destination|visitors is not in an ARRAY-format, but rather in a STRUCT-format, so you do not need UNNEST it. Also view this thread please :)
SELECT
origin,
COUNT(struct.destination),
COUNT(struct.visitors)
FROM dataset.table
GROUP BY 1
Reddit
reddit.com › r/bigquery › resources for learning struct, array, unnest
r/bigquery on Reddit: Resources for learning STRUCT, ARRAY, UNNEST
September 6, 2024 -
Hi,
I just started a new internship and wanted to learn how to use STRUCT, ARRAY and UNNEST.
I have some Python knowledge and I understand that ARRAY is something like a Python list, but I just can't wrap my head around STRUCT. I don't really understand the concept and the materials I find on the internet are just not speaking to me.
Does anyone have some resources that helped you understand how to work with STRUCT, ARRAY and UNNEST?
Top answer 1 of 3
3
If you’re familiar with basic DB concepts, “STRUCT” is just the BQ-SQL term for “Record”. It’s a container with a fixed, named set of slots that you define. Conceptually, for Arrays and Structs, think of a stacked set of toolboxes, where each toolbox has the exact same layout inside. Each identical toolbox is a struct, and the entire joined stack is an array. You put things “into” individual fields of a struct using STRUCT assignments, and you get them “out” using dot notation (“stuct_name.field_name”). You stack/combine multiple structs “into” a single array using the “ARRAY_AGG()” function. You get those records back “out” using “UNNEST”, which really just transforms the array into a table, that you can then query.
2 of 3
2
Think of struct like a dictionary or named tuples You can get the basics directly from the docs; https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct_type
sql - How can I unnest a string of an array of structs in bigquery? - Stack Overflow
The bigquery method: INFORMATION_SCHEMA.TABLE_OPTIONS returns a string of arrays of structs for option_value when option_name is "labels". More information is here: TABLE_OPTIONS For exam... More on stackoverflow.com
Unnest an Array
Im working on GCP Billing queries in BQ. But while extracting array with the cost I'm getting wrong values like unnest returns array elements in row… More on reddit.com
1
3
November 19, 2018Resources for learning STRUCT, ARRAY, UNNEST
If you’re familiar with basic DB concepts, “STRUCT” is just the BQ-SQL term for “Record”. It’s a container with a fixed, named set of slots that you define. Conceptually, for Arrays and Structs, think of a stacked set of toolboxes, where each toolbox has the exact same layout inside. Each identical toolbox is a struct, and the entire joined stack is an array. You put things “into” individual fields of a struct using STRUCT assignments, and you get them “out” using dot notation (“stuct_name.field_name”). You stack/combine multiple structs “into” a single array using the “ARRAY_AGG()” function. You get those records back “out” using “UNNEST”, which really just transforms the array into a table, that you can then query. More on reddit.com
3
3
September 6, 2024Structs in Big Query
I think the syntax is along the lines of select cust_struct.id, cust_struct.identifier from unnest(customers) as cust_struct. More on reddit.com
4
3
October 31, 2023Videos
06:22
Using UNNEST & STRUCT with ARRAYS to create dynamic tables in SQL ...
02:56
14: UNNEST, ARRAY, STRUCT | SQL Tutorial - YouTube
14: UNNEST, ARRAY, STRUCT | SQL Tutorial
26:08
2 Google BigQuery Array (a.k.a. repeated field) & Struct (a.k.a. ...
GCP - Structures (STRUCT) in BigQuery - Do it yourself - DIY#12
11:23
GCP - Arrays Explained in BigQuery - Do it yourself - DIY#11 - YouTube
Learn BigQuery!
scosco.github.io › learn-bigquery › foundation › subqueries.html
Subqueries: Arrays + UNNEST() | Learn BigQuery!
Using Structs means to have a fixed ... Every struct provides the columns of their respective row. To sum up: UNNEST() turns array elements into rows and struct elements (within arrays) into columns....
Medium
medium.com › data-engineers-notes › unnesting-arrays-in-bigquery-c1b48d413ece
UNNESTING ARRAYS in BigQuery
April 15, 2024 - If we want to perform operations (say find out what was the earliest enrollment date, or count the distinct activities that the members are enrolled in), we’d need to unpack these rows by UNNESTing the ARRAY where activities are stored it.
Count
count.co › sql-resources › bigquery-standard-sql › unnest
UNNEST | BigQuery Standard SQL - Count.co
WITH fruit_baskets as ( SELECT * FROM UNNEST( ARRAY<STRUCT<fruit ARRAY<STRING>,basket STRING>>[ (['bananas', 'apples', 'oranges'], 'basket 1'), (['bananas', 'oranges'], 'basket 2'), (['bananas', 'apples'], 'basket 3') ] ) AS fruit ) SELECT * FROM fruit_baskets CROSS JOIN UNNEST(fruit) as fruit_unnest
Google
docs.cloud.google.com › bigquery › work with arrays
Work with arrays | BigQuery | Google Cloud Documentation
If a table contains an ARRAY of STRUCTs, you can flatten the ARRAY to query the fields of the STRUCT. You can also flatten ARRAY type fields of STRUCT values. The following example uses UNNEST with INNER JOIN to flatten an ARRAY of STRUCTs.
Sheetgo
sheetgo.com › home › how to use unnest in bigquery
How to use unnest in BigQuery - Sheetgo
December 17, 2025 - 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.
Google Cloud
cloud.google.com › bigquery › use nested and repeated fields
Use nested and repeated fields | BigQuery | Google Cloud Documentation
In BigQuery, you typically specify a nested schema as an ARRAY of STRUCT objects. You use the UNNEST operator to flatten the nested data, as shown in the following query:
Towards Data Science
towardsdatascience.com › home › latest › bigquery unnest: how to work with nested data in bigquery
BigQuery UNNEST: How to work with nested data in BigQuery | Towards Data Science
January 24, 2025 - Again, we use the BigQuery UNNEST function to achieve this. The basic approach is to: ... Reconstruct the structure using the ARRAY and STRUCT datatypes accordingly.
Firebase
firebase.blog › posts › 2017 › 03 › bigquery-tip-unnest-function
BigQuery Tip: The UNNEST Function
March 22, 2017 - #standardSQL WITH data AS ( SELECT "primes under 15" AS description, [1,2,3,5,7,11,13] AS primes_array) SELECT description, prime FROM data CROSS JOIN UNNEST (primes_array) as prime · What you’re basically saying is, “Hey, BigQuery, please break up that primes_array into its individual members. Then join each of these members with a clone of the original row.” So you end up with a data structure that looks more like this:
MarquinSmith
marquinsmith.com › home › data analysis › unnesting in bigquery – a guided introduction
UNNESTing in BigQuery – a guided introduction | MarquinSmith
October 5, 2023 - The UNNEST function in BigQuery is used to transform arrays or repeated fields within a table into individual rows. This is particularly useful when dealing with data stored in nested structures, such as arrays or structs, as it allows you to ...
The Digital Skye
thedigitalskye.com › 2021 › 01 › 21 › explore-arrays-and-structs-for-better-performance-in-google-bigquery
Explore Arrays and Structs for Better Query Performance in Google BigQuery – The Digital Skye
January 21, 2021 - #standardSQL WITH unnested_table AS ( # Unpack cuisine_array SELECT name, location, cuisine FROM `array-and-struct.array.restaurant_cuisine`, UNNEST(cuisine_array) AS cuisine # Filter only restaurants contain "Casual" labels WHERE "Casual" IN UNNEST(cuisine_array)) # Re-group cuisine into array format SELECT name, location, ARRAY_AGG(cuisine) AS cuisine_array FROM unnested_table GROUP BY name, location; ... Structs are flexible containers of ordered fields each with a type (required) and a name (optional).From Google Cloud · Contrasting with arrays, you can store multiple data types in a Struct, even Arrays. In Google BigQuery, a Struct is a parent column representing an object that has multiple child columns.
The Firebase Blog
firebase.googleblog.com › 2017 › 03 › bigquery-tip-unnest-function.html
The Firebase Blog: BigQuery Tip: The UNNEST Function
March 22, 2017 - BigQuery Tip: The UNNEST Function · Take Control of Your Firebase Init on Android · Profiling your Realtime Database Performance · Multiplying the Power of Firebase Storage · Introducing Cloud Functions for Firebase · Working Closer with Google Cloud Platform ·
Google Cloud
cloud.google.com › bigquery › query syntax
Query syntax | BigQuery | Google Cloud Documentation
Because the UNNEST operator returns a value table, you can alias UNNEST to define a range variable that you can reference elsewhere in the query. If you reference the range variable in the SELECT list, the query returns a struct containing all of the fields of the original struct in the input table.
OneUptime
oneuptime.com › home › blog › how to flatten nested and repeated fields in bigquery with unnest
How to Flatten Nested and Repeated Fields in BigQuery with UNNEST
February 17, 2026 - UNNEST is the essential tool for working with nested and repeated fields in BigQuery. Whether you are flattening simple arrays, navigating multi-level nested structs, or working with complex exports like Google Analytics data, UNNEST lets you ...
Google Skills
skills.google › focuses › 3696
Working with JSON, Arrays, and Structs in BigQuery | Google Skills
December 16, 2025 - Load and query semi-structured data including unnesting.
Top answer 1 of 2
6
Consider below approach
select table_catalog, table_schema, table_name,
array(
select as struct arr[offset(0)] key, arr[offset(1)] value
from unnest(regexp_extract_all(option_value, r'STRUCT\(("[^"]+", "[^"]+")\)')) kv,
unnest([struct(split(replace(kv, '"', ''), ', ') as arr)])
) options
from sample_dataset.INFORMATION_SCHEMA.TABLE_OPTIONS
where table_name = 'sample_data'
and option_name = 'labels'
if applied to sample use case in your question - output is
2 of 2
0
Another option to the nice solution offered by @Mikhail_Berlyant would be to use the TO_JSON function when it will be available.
Since January 2022, JSON is now a data type supported by BigQuery, but is not yet Generally Available.
When it becomes GA (or if you are lucky enough to already have access, I think it's possible to apply) you can simply do:
SELECT * EXCEPT (option_value),
TO_JSON(option_value) AS option_value_json
FROM sample_table.INFORMATION_SCHEMA.TABLE_OPTIONS
And you will be able to extract the information you need very simply with the BQ JSON function.
Some refs: here and here