BigQuery STRUCT vs ARRAY: Handling Nested and Repeated Data
By Sharon Ben-Moshe · October 11, 2026 · 1 min read
Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Understand Google BigQuery's nested STRUCT and repeated ARRAY data types. Learn how to query, construct, and flatten complex nested schemas.
Quick answer
In Google BigQuery, a STRUCT represents nested data (a single container with named, typed fields, similar to an object or record), while an ARRAY represents repeated data (an ordered list of zero or more elements of the same data type). Combining them as ARRAY<STRUCT<...>> allows BigQuery to represent complex hierarchical data without relational normalization joins.
FAQs
How do I access a field inside a BigQuery STRUCT?
Use standard dot notation: `address.city` or `user_profile.settings.theme`.
How do I access items inside an ARRAY?
To extract an element by offset, use `array_col[OFFSET(0)]` or `array_col[ORDINAL(1)]`. To flatten the array into individual rows for joining, use `CROSS JOIN UNNEST(array_col)`.
Can an ARRAY contain elements of different data types in BigQuery?
No. All elements in a BigQuery ARRAY must share the identical data type. If you need mixed types, create an ARRAY of STRUCTs where each struct field has its own type.
Why does BigQuery use nested and repeated data instead of foreign key joins?
Denormalizing hierarchical data into nested records improves analytical query performance by co-locating related data in BigQuery's columnar storage (Capacitor), eliminating expensive distributed shuffle joins.
Keep reading
The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability
By Sharon Ben-Moshe · Oct 13, 2026
The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.
Read postThe Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes
By Sharon Ben-Moshe · Oct 13, 2026
The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.