Flattening Nested Data in BigQuery with UNNEST: 10 Code-Along Examples

The UNNEST article explains a feature that separates BigQuery from ordinary SQL databases: a single cell can hold many values, because a column can be an array, a struct, or an array of structs. That nesting keeps related data together and avoids joins, but it means a row is no longer flat, and to query the contents you must first turn them back into rows. UNNEST is that operation. This workbook covers the whole toolkit: flattening a literal array, the comma-join pattern that pairs each row with its own array elements, struct dot notation, the LEFT JOIN form that saves rows with empty arrays, WITH OFFSET for position, two-level nesting, aggregation, re-nesting with ARRAY_AGG, and the subquery form that computes over an array without flattening anything.

Run the setup once, then every example runs in the BigQuery console.

Setup: run this once

CREATE SCHEMA IF NOT EXISTS scratch;
-- orders with a STRUCT customer and an ARRAY of STRUCT items,
-- each item carrying its own ARRAY of tags
CREATE OR REPLACE TABLE scratch.orders AS
SELECT
  'A-1' AS order_id,
  STRUCT('Ana' AS name, 'UK' AS country) AS customer,
  [STRUCT('Keyboard' AS product, 2 AS qty, 40 AS price, ['tech','input'] AS tags),
   STRUCT('Mouse'    AS product, 1 AS qty, 25 AS price, ['tech'] AS tags)] AS items
UNION ALL SELECT
  'A-2',
  STRUCT('Ben' AS name, 'UK' AS country),
  [STRUCT('Monitor' AS product, 1 AS qty, 200 AS price, ['tech','display'] AS tags)]
UNION ALL SELECT
  'A-3',
  STRUCT('Cara' AS name, 'DE' AS country),
  [STRUCT('Desk'  AS product, 1 AS qty, 150 AS price, ['furniture'] AS tags),
   STRUCT('Chair' AS product, 2 AS qty,  90 AS price, ['furniture','ergonomic'] AS tags),
   STRUCT('Lamp'  AS product, 1 AS qty,  30 AS price, ARRAY<STRING>[] AS tags)]
UNION ALL SELECT
  'A-4',
  STRUCT('Dan' AS name, 'US' AS country),
  ARRAY<STRUCT<product STRING, qty INT64, price INT64, tags ARRAY<STRING>>>[];
-- two parallel arrays aligned by position, for the WITH OFFSET example
CREATE OR REPLACE TABLE scratch.surveys AS
SELECT 'S-1' AS survey_id, ['speed','price','support'] AS questions, [5, 3, 4] AS scores
UNION ALL SELECT 'S-2', ['speed','price'], [2, 5];

Four orders, and two deliberate awkward cases: order A-4 has an empty items array, and the Lamp in A-3 has empty tags. Both matter later.

1. UNNEST a literal array

At its simplest, UNNEST takes an array and returns one row per element. Running it on a literal is the clearest way to see what the operation does.

SELECT color
FROM UNNEST(['red', 'green', 'blue']) AS color;

color
------
red
green
blue

One array in, three rows out. The alias after AS names the column holding each element, so color is a plain string you can filter, group, or join on like any other. This is the whole mechanism, and everything that follows is the same operation applied to an array that lives in a table column rather than typed inline. Note that row order is not guaranteed by UNNESTalone, which is why Example 5 introduces WITH OFFSET when position matters.

2. Flattening an array column with the comma join

When the array is a column, you list the table and the UNNEST separated by a comma. This pairs every row with its own array elements, so an order with three items becomes three rows.

SELECT
  o.order_id,
  item.product,
  item.qty
FROM scratch.orders AS o, UNNEST(o.items) AS item
ORDER BY o.order_id, item.product;

order_id | product  | qty
---------+----------+----
A-1      | Keyboard | 2
A-1      | Mouse    | 1
A-2      | Monitor  | 1
A-3      | Chair    | 2
A-3      | Desk     | 1
A-3      | Lamp     | 1

Six rows from four orders, one per line item, and the order_id repeats down the group because it belongs to the parent row. The comma is shorthand for CROSS JOIN, but it is a correlated one: UNNEST(o.items) refers to the current row’s array, so each order is joined only to its own items rather than to every item in the table. Because the items are structs, dot notation reaches inside them for item.product and item.qty. And notice who is missing: order A-4 has an empty array, so a cross join produces nothing for it and Dan vanishes, which the next-but-one example fixes.

3. Structs need no UNNEST

A struct holds one set of named fields, not many values, so it never multiplies rows. Reach into it with dot notation and the row count stays exactly as it was.

SELECT
  order_id,
  customer.name,
  customer.country
FROM scratch.orders
ORDER BY order_id;

order_id | name | country
---------+------+--------
A-1      | Ana  | UK
A-2      | Ben  | UK
A-3      | Cara | DE
A-4      | Dan  | US

Four orders in, four rows out, including Dan, because nothing here flattens anything. This is the distinction the article draws and the one that saves the most confusion: an array is many values and needs UNNEST to become rows, while a struct is one structured value whose fields you simply address by name. Reaching for UNNEST on a struct is a common early mistake, and the giveaway is that you only ever want one value from it per row.

4. LEFT JOIN UNNEST keeps the empty ones

Swap the comma for LEFT JOIN and rows with empty or null arrays survive, with nulls where their elements would have been. This is the difference between “orders that have items” and “all orders”.

SELECT
  o.order_id,
  o.customer.name,
  item.product
FROM scratch.orders AS o
LEFT JOIN UNNEST(o.items) AS item
ORDER BY o.order_id, item.product;

order_id | name | product
---------+------+---------
A-1      | Ana  | Keyboard
A-1      | Ana  | Mouse
A-2      | Ben  | Monitor
A-3      | Cara | Chair
A-3      | Cara | Desk
A-3      | Cara | Lamp
A-4      | Dan  | NULL

Seven rows now, and Dan has reappeared with a null product, because a left join keeps the outer row even when the array supplies nothing to join to. The choice between the comma and LEFT JOIN is a genuine decision about meaning rather than style: use the comma when a row without array elements is irrelevant to the question, and LEFT JOIN UNNEST when it is part of the answer, as in “how many orders contained no items” or any count that must reconcile back to the original row total.

5. WITH OFFSET: the position of each element

Arrays are ordered, and WITH OFFSET exposes each element’s zero-based index as a column. That index is how you recover position after flattening.

SELECT
  o.order_id,
  idx,
  item.product
FROM scratch.orders AS o, UNNEST(o.items) AS item WITH OFFSET AS idx
ORDER BY o.order_id, idx;

order_id | idx | product
---------+-----+---------
A-1      | 0   | Keyboard
A-1      | 1   | Mouse
A-2      | 0   | Monitor
A-3      | 0   | Desk
A-3      | 1   | Chair
A-3      | 2   | Lamp

The offset restarts at 0 for every parent row, so it is a position within that order’s array rather than a global row number. This matters whenever the array’s order carries meaning, a sequence of events, a ranked list, steps in a funnel, and it gives you the handle for “the first item in each order” (WHERE idx = 0) or for reconstructing the original ordering after a group-by has scrambled it. Its other use is aligning two arrays, which is the next example.

6. Joining parallel arrays by position

Some schemas store related values in two arrays that line up by index. Unnesting both with offsets and matching the indexes is how you pair them back together.

SELECT
  s.survey_id,
  q AS question,
  sc AS score
FROM scratch.surveys AS s,
     UNNEST(s.questions) AS q  WITH OFFSET AS q_idx,
     UNNEST(s.scores)    AS sc WITH OFFSET AS s_idx
WHERE q_idx = s_idx          -- the alignment; without it you get a cross product
ORDER BY s.survey_id, q_idx;

survey_id | question | score
----------+----------+------
S-1       | speed    | 5
S-1       | price    | 3
S-1       | support  | 4
S-2       | speed    | 2
S-2       | price    | 5

Five rows, each question sitting beside its own score. The WHERE q_idx = s_idx line is load-bearing rather than decorative: unnesting two arrays in the same query produces every combination of their elements, so without it survey S-1 alone would return nine rows pairing each of three questions with each of three scores. Matching the offsets keeps only the pairs that were aligned in the source data. If you have a choice when designing a schema, prefer a single array of structs over parallel arrays, precisely so this alignment step is never needed.

7. Two levels of nesting

An array inside an array of structs needs two UNNEST clauses, chained left to right, with the second one referring to the alias created by the first.

SELECT
  o.order_id,
  item.product,
  tag
FROM scratch.orders AS o,
     UNNEST(o.items) AS item,        -- level 1: orders -> items
     UNNEST(item.tags) AS tag        -- level 2: items -> tags
ORDER BY o.order_id, item.product, tag;

order_id | product  | tag
---------+----------+-----------
A-1      | Keyboard | input
A-1      | Keyboard | tech
A-1      | Mouse    | tech
A-2      | Monitor  | display
A-2      | Monitor  | tech
A-3      | Chair    | ergonomic
A-3      | Chair    | furniture
A-3      | Desk     | furniture

Eight rows, one per item-and-tag combination, and the chain reads as a path down the schema: each order fans out to its items, and each item fans out to its tags. The alias matters, since UNNEST(item.tags) only works because item was defined by the clause before it, which is why the order of the comma-separated clauses is not arbitrary. Look for what is missing: the Lamp has no tags, so the second cross join produces nothing for it and it disappears entirely from a result that was supposed to list every item. Every level of comma-joined UNNEST is another place empties are silently dropped, and LEFT JOIN UNNEST at that level is the fix.

8. Filter and aggregate the flattened rows

Once flattened, array contents behave like ordinary rows, so WHERE filters them and GROUP BY rolls them back up. This is where nested data becomes analysis.

SELECT
  o.order_id,
  o.customer.name,
  COUNT(*) AS line_items,
  SUM(item.qty * item.price) AS order_total
FROM scratch.orders AS o, UNNEST(o.items) AS item
WHERE item.price >= 30                    -- filters the flattened item rows
GROUP BY o.order_id, o.customer.name
ORDER BY order_total DESC;

order_id | name | line_items | order_total
---------+------+------------+------------
A-3      | Cara | 3          | 360
A-2      | Ben  | 1          | 200
A-1      | Ana  | 1          | 80

The filter applies at the item level, so Ana’s £25 Mouse is excluded and her order total falls to the £80 of Keyboards alone, while Cara keeps all three of her items. The grouping then reassembles per-order figures from the flattened rows, which is the standard shape: flatten, filter, aggregate back up. One caution the row counts expose, which is worth carrying into every nested query: COUNT(*) here counts item rows, not orders, and SUM adds across items. If you also joined a second array you would be double-counting, which is the classic nested-data reporting bug.

9. Re-nesting with ARRAY_AGG

ARRAY_AGG is the inverse of UNNEST: it collapses grouped rows back into an array, which is how you build nested output or produce compact one-row-per-order summaries.

SELECT
  o.order_id,
  ARRAY_AGG(item.product ORDER BY item.product) AS products,
  ARRAY_AGG(STRUCT(item.product, item.qty) ORDER BY item.product) AS lines
FROM scratch.orders AS o, UNNEST(o.items) AS item
GROUP BY o.order_id
ORDER BY o.order_id;

order_id | products                  | lines
---------+---------------------------+------------------------------------------
A-1      | [Keyboard, Mouse]         | [{Keyboard, 2}, {Mouse, 1}]
A-2      | [Monitor]                 | [{Monitor, 1}]
A-3      | [Chair, Desk, Lamp]       | [{Chair, 2}, {Desk, 1}, {Lamp, 1}]

Back to one row per order, with the item detail preserved inside arrays rather than thrown away by the aggregation. The ORDER BY inside ARRAY_AGG is worth using habitually, because array order is otherwise arbitrary and results become non-reproducible. Wrapping columns in STRUCT before aggregating, as the second column does, rebuilds an array of structs, which is how you write a nested table from flat data or hand a nested payload back to an application. Flatten to compute, re-nest to store.

10. Computing over an array without flattening

Often you want a number per row, not a row per element. A subquery over UNNEST in the SELECT list does exactly that, leaving the row count untouched.

SELECT
  order_id,
  ARRAY_LENGTH(items) AS item_count,
  (SELECT SUM(i.qty * i.price) FROM UNNEST(items) AS i) AS order_total,
  (SELECT STRING_AGG(i.product, ', ' ORDER BY i.product)
     FROM UNNEST(items) AS i) AS product_list
FROM scratch.orders
ORDER BY order_id;

order_id | item_count | order_total | product_list
---------+------------+-------------+---------------------
A-1      | 2          | 105         | Keyboard, Mouse
A-2      | 1          | 200         | Monitor
A-3      | 3          | 360         | Chair, Desk, Lamp
A-4      | 0          | NULL        | Chair... 

One row, because only Cara’s order contains a furniture-tagged item. This scalar-subquery style is both clearer and cheaper than flattening and regrouping when you only need a per-row answer, and it is the efficiency advice the article closes on: filter and compute as close to the nested structure as you can, select only the fields you need, and flatten only when the analysis genuinely requires one row per element.

Work through these and you have the whole article in practice: UNNEST on literals and columns, the correlated comma join, struct dot notation, LEFT JOIN UNNEST for empty arrays, WITH OFFSET for position and parallel-array alignment, two-level chains and the empties they drop, filtering and aggregating flattened rows, ARRAY_AGG for re-nesting, and scalar subqueries that compute over arrays in place. The mental model to keep is the article’s: arrays need flattening and structs do not, every comma-joined UNNEST both multiplies rows and quietly drops empty ones, and the cleanest nested queries are the ones that flatten as late and as little as possible.

See you soon.