BigQuery UNNEST: How to Flatten Arrays and Nested Data

BigQuery UNNEST takes an array and turns each element into its own row, so you can filter, join and aggregate the values that were nested inside a single record. You write it in the FROM clause, usually correlated with the parent table: SELECT id, item FROM orders, UNNEST(items) AS item. This is the piece most people miss when they first hit BigQuery, because BigQuery stores repeated data as arrays instead of splitting it into extra tables. Below is the syntax, how UNNEST works with arrays of structs, how to flatten a JSON array, when to use LEFT JOIN UNNEST, and the error you will see if you forget it.

What does UNNEST do in BigQuery?

UNNEST converts an array of N elements into a table of N rows, one element per row. BigQuery lets a single column hold an array, so an order row can carry a list of line items without a separate line-items table. To query those items with normal SQL, you flatten the array into rows first, and UNNEST is the operator that does it.

-- one order row with an array of three items becomes three rows
SELECT
  order_id,
  item
FROM sales.orders,
  UNNEST(items) AS item

The comma between the table and UNNEST is a correlated cross join: for each order row, BigQuery joins in the rows produced by unnesting that order's own array. You can write it as CROSS JOIN UNNEST(items) and it means the same thing. After the UNNEST, item is a scalar you can put in SELECT, WHERE, GROUP BY, anywhere.

UNNEST an array of structs

Most real nested data is not an array of plain strings, it is an array of structs, where each element has its own fields. Event tables and the GA4 export are full of these. Once you unnest, you reach the fields with dot notation on the alias.

SELECT
  order_id,
  line.sku,
  line.quantity,
  line.price
FROM sales.orders,
  UNNEST(line_items) AS line
WHERE line.quantity > 1

Here line_items is an ARRAY<STRUCT<sku STRING, quantity INT64, price NUMERIC>>. After unnesting, each line is one struct and line.sku reads a field off it. You can aggregate across all items from all orders in the same query, for example SUM(line.quantity * line.price) grouped by day.

Keep the array index with WITH OFFSET

Sometimes the position matters: the first item is the primary one, or you want to reconstruct order. WITH OFFSET gives you a zero-based index alongside each element.

SELECT
  order_id,
  item,
  idx
FROM sales.orders,
  UNNEST(items) AS item WITH OFFSET AS idx
WHERE idx = 0   -- just the first item in each array

UNNEST vs LEFT JOIN UNNEST: keeping empty rows

A plain comma or CROSS JOIN UNNEST drops any parent row whose array is empty or NULL, because an empty array produces zero rows to join. If you need every order to survive even when it has no items, use a LEFT JOIN.

This is the same distinction as an inner join versus a left join, and forgetting it is a classic way to lose rows and undercount. If your totals look low after an UNNEST, this is the first thing to check.

Flatten a JSON array

If the nested data arrived as JSON rather than a native ARRAY, extract it into an array first, then unnest that. This pattern shows up constantly when you land raw event payloads, or the output of a web scraping pipeline that returns clean, structured data, into a single JSON column.

-- json_col holds something like {"tags": ["a", "b", "c"]}
SELECT
  id,
  tag
FROM raw.events,
  UNNEST(JSON_QUERY_ARRAY(json_col, '$.tags')) AS tag

Use JSON_QUERY_ARRAY (or JSON_EXTRACT_ARRAY on the legacy JSON functions) to pull the array out of the JSON, then UNNEST it exactly like a native array. Each element still comes back as JSON, so wrap it in a conversion like JSON_VALUE(tag) if you want a plain string.

The error you will see if you forget UNNEST

If you try to select a field off an array without unnesting it, BigQuery stops you with a message like Cannot access field sku on a value with type ARRAY<STRUCT<...>>. That error is BigQuery telling you the column is repeated and you have to flatten it first. Add the array to an UNNEST in the FROM clause and reference the alias, not the raw column.

The mirror-image mistake is unnesting when you did not need to. If you only want a count of items, ARRAY_LENGTH(items) gives it directly without flattening. Unnest when you need to reach inside the elements; use the array functions when you only need a fact about the array as a whole.

Ask it in plain English instead

Nested and repeated columns are the single biggest reason BigQuery SQL trips people up, because the array has to be flattened before a normal query will touch it. Agentsql connects read-only to BigQuery, works out where an UNNEST belongs from a plain-English question like "total revenue by product SKU last month," writes the correct Standard SQL, and shows you the query so an analyst can check the joins. See the BigQuery integration for how it connects and the SQL query generator for how the translation works, then see how it works and ask BigQuery a question in plain English.