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
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, 2023How to refer those columns
Thanks for your submission to r/BigQuery . Did you know that effective July 1st, 2023, Reddit will enact a policy that will make third party reddit apps like Apollo, Reddit is Fun, Boost, and others too expensive to run? On this day, users will login to find that their primary method for interacting with reddit will simply cease to work unless something changes regarding reddit's new API usage policy. Concerned users should take a look at r/modcoord . I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns. More on reddit.com
7
3
November 29, 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....
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
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.
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 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.
Google
docs.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.
Google Skills
skills.google › focuses › 28347
Working with JSON and Array data in BigQuery 2.5 | Google Skills
STRUCTs (and ARRAYs) must be unpacked before you can operate over their elements. Wrap an UNNEST() around the name of the struct itself or the struct field that is an array in order to unpack and flatten it.
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 ...