BigQuery Arrays & Structs
Everything you need to work effectively with nested and repeated data in BigQuery — ARRAY, STRUCT, UNNEST, ARRAY_AGG, and related functions.
- Flattening JSON arrays in BigQuery
Learn how to use BigQuery's JSON_FLATTEN to handle complex JSON arrays efficiently without losing data context when hierarchy isn't important
- Why you should think twice before UNNESTing arrays or date intervals
Avoid unnecessary unnesting of arrays and date intervals in BigQuery to improve efficiency and manage data cardinality effectively
- How ARRAY() can function as UNPIVOT and UNNEST as PIVOT?
Learn how to transform SQL data with ARRAY and UNNEST functions for tasks similar to UNPIVOT and PIVOT operations
- A practical exercise working with ARRAYS and correlated subqueries in BigQuery
A practical BigQuery exercise combining ARRAYS, UNNEST, and correlated subqueries to compute per-customer product diversity scores from order history data.
- The relationship between ARRAY_AGG and UNNEST
ARRAY_AGG turns rows into arrays, and UNNEST turns arrays back into rows. Learn how they work together in BigQuery with clear examples.
- Pay attention to cardinality & grain when UNNESTING in BigQuery!
UNNESTing two unrelated arrays in the same BigQuery query produces a Cartesian product, multiplying row counts unexpectedly.
- Constructing STRUCTS in BigQuery
BigQuery has three STRUCT syntax forms: tuple, untyped STRUCT(), and typed STRUCT<T>. Learn when to use each and why field order matters in comparisons.
- Using STRUCTS for quick analysis in BigQuery
Wrap multiple filter conditions into an array of STRUCTs in BigQuery to check several test cases in a single query pass.
- Understanding STRUCTS in BigQuery
BigQuery STRUCTs group named fields of mixed types into one column. Learn to create them with STRUCT(), access nested fields, and UNNEST arrays of STRUCTs.
- Accessing ARRAY elements in BigQuery
Access BigQuery arrays with OFFSET or ORDINAL, and use SAFE_OFFSET or SAFE_ORDINAL to return NULL instead of failing on out-of-bounds indexes.
- Enumerating ARRAY elements in BigQuery using WITH OFFSET
WITH OFFSET pairs each array element with its 0-based index after UNNEST. Use it to preserve order, filter by position, or locate specific elements.
- UNNESTING ARRAYS in BigQuery
UNNEST in BigQuery expands array columns into individual rows, enabling aggregations and filtering on nested data.
- Leveraging ARRAYS in BigQuery for query performance
Compares flat row storage versus nested ARRAY storage in BigQuery using a 100-customer dataset benchmark. ARRAYs cut slot time to a fraction of the flat...
- Using ARRAY_CONCAT_AGG() in BigQuery
ARRAY_CONCAT_AGG collapses multiple arrays into one flat array per group. Use it when ARRAY_AGG produces nested arrays instead of the flat result you need.
- SELECT AS STRUCT and SELECT AS VALUE
Explains the difference between SELECT AS STRUCT and SELECT AS VALUE in BigQuery and when to use each. Covers value tables, UNNEST scenarios, and using...
- Using STRUCTS for Audit Fields in BigQuery
Shows how to use nested STRUCT columns in BigQuery to store audit metadata without cluttering your schema. Practical pattern for tracking source event IDs...
- Using ARRAY_AGG in BigQuery
ARRAY_AGG collapses multiple rows into one array per group. Control it with ORDER BY, DISTINCT, LIMIT, and STRUCT to shape exactly the output you need.
- Pay attention to this when UNNESTING in BigQuery
In BigQuery, comma syntax before UNNEST behaves like CROSS JOIN and can drop rows with empty arrays. Compare it with LEFT JOIN UNNEST side by side.
- Converting JSON to BigQuery ARRAY and STRUCT
Learn how to convert JSON strings into BigQuery ARRAY and STRUCT types using JSON_VALUE and JSON_EXTRACT_ARRAY.