# UNNEST in BigQuery

> UNNEST turns an array into rows. A comma or CROSS JOIN with UNNEST drops rows whose array is empty, LEFT JOIN keeps them, and WITH OFFSET keeps the element order.

Source: https://docs.querylab.io/bigquery/unnest/

Updated: 2026-10-14

`UNNEST` takes an array and returns a table with one row per element. You use it in `FROM` to flatten an array column into rows, and in subqueries to search or sum inside an array without flattening the whole table. BigQuery stores repeated data as arrays, so `UNNEST` is how you query it.

**an array literal as rows**

```sql
SELECT fruit
FROM UNNEST(['apple', 'pear', 'plum']) AS fruit
```

Result in BigQuery (3 rows):

| fruit |
| --- |
| apple |
| pear |
| plum |

## Flattening an array column

Put `UNNEST` after a comma in `FROM`. For each order, BigQuery unnests that order's own `items` and pairs each item with its order. The comma is a `CROSS JOIN` whose right side depends on the current row of the left side.

**one row per item**

```sql
WITH orders AS (
  SELECT 'o1' AS order_id, ['apple', 'pear'] AS items UNION ALL
  SELECT 'o2', ['plum'] UNION ALL
  SELECT 'o3', CAST([] AS ARRAY<STRING>)
)
SELECT order_id, item
FROM orders, UNNEST(items) AS item
ORDER BY order_id, item
```

Result in BigQuery (3 rows):

| order_id | item |
| --- | --- |
| o1 | apple |
| o1 | pear |
| o2 | plum |

Order `o3` has an empty array, and it is gone. A cross join with zero rows produces zero rows, so every row whose array is empty or NULL disappears. This is the most common `UNNEST` mistake: a count of orders after unnesting is silently lower than the real count.

In Querylab.io, a table's SQL Templates include "SELECT all columns (unnested)", which writes the `UNNEST` joins for the table's ARRAY columns ([DDL operations](https://docs.querylab.io/schema-browser/ddl-operations/)).

To keep those rows, use `LEFT JOIN UNNEST(...)`. The order stays, with NULL for the item:

**LEFT JOIN keeps the order with no items**

```sql
WITH orders AS (
  SELECT 'o1' AS order_id, ['apple', 'pear'] AS items UNION ALL
  SELECT 'o2', ['plum'] UNION ALL
  SELECT 'o3', CAST([] AS ARRAY<STRING>)
)
SELECT order_id, item
FROM orders
LEFT JOIN UNNEST(items) AS item
ORDER BY order_id, item
```

Result in BigQuery (4 rows):

| order_id | item |
| --- | --- |
| o1 | apple |
| o1 | pear |
| o2 | plum |
| o3 | NULL |

`LEFT JOIN UNNEST` needs no `ON` clause, because the array already belongs to the row.

### The short form

Inside `FROM`, you can name the array column directly after the comma, without writing `UNNEST`. This implicit form behaves exactly like the comma join above:

**FROM orders, orders.items**

```sql
WITH orders AS (
  SELECT 'o1' AS order_id, ['apple', 'pear'] AS items UNION ALL
  SELECT 'o2', ['plum'] UNION ALL
  SELECT 'o3', CAST([] AS ARRAY<STRING>)
)
SELECT order_id, item
FROM orders, orders.items AS item
ORDER BY order_id, item
```

Result in BigQuery (3 rows):

| order_id | item |
| --- | --- |
| o1 | apple |
| o1 | pear |
| o2 | plum |

## Keeping the element order

`UNNEST` doesn't guarantee the order of the rows it produces. When the position matters, add `WITH OFFSET`, which returns each element's zero-based index as an extra column, and order by it:

**WITH OFFSET gives each element's position**

```sql
SELECT step, position
FROM UNNEST(['open', 'add to cart', 'pay']) AS step WITH OFFSET AS position
ORDER BY position
```

Result in BigQuery (3 rows):

| step | position |
| --- | --- |
| open | 0 |
| add to cart | 1 |
| pay | 2 |

## Arrays of structs

When the elements are structs, give the unnested row an alias and read its fields with a dot:

**order lines as rows**

```sql
WITH orders AS (
  SELECT 'o1' AS order_id, [STRUCT('apple' AS sku, 3 AS qty), STRUCT('pear', 1)] AS lines UNION ALL
  SELECT 'o2', [STRUCT('plum', 6)]
)
SELECT order_id, line.sku, line.qty
FROM orders, UNNEST(lines) AS line
ORDER BY order_id, line.sku
```

Result in BigQuery (3 rows):

| order_id | sku | qty |
| --- | --- | --- |
| o1 | apple | 3 |
| o1 | pear | 1 |
| o2 | plum | 6 |

`SELECT *` over an unnested array of structs returns one column per struct field, not a single struct column:

**SELECT * expands the struct fields**

```sql
SELECT *
FROM UNNEST([STRUCT('apple' AS sku, 3 AS qty), STRUCT('pear', 1)])
```

Result in BigQuery (2 rows):

| sku | qty |
| --- | --- |
| apple | 3 |
| pear | 1 |

## Working in an array without flattening

Often you don't want one row per element, only a value computed from the array. A subquery over `UNNEST` runs once per row, and the table keeps its shape:

**a total and a check per order**

```sql
WITH orders AS (
  SELECT 'o1' AS order_id, [STRUCT('apple' AS sku, 3 AS qty), STRUCT('pear', 1)] AS lines UNION ALL
  SELECT 'o2', [STRUCT('plum', 6)]
)
SELECT
  order_id,
  (SELECT SUM(qty) FROM UNNEST(lines)) AS total_qty,
  'pear' IN (SELECT sku FROM UNNEST(lines)) AS has_pear
FROM orders
ORDER BY order_id
```

Result in BigQuery (2 rows):

| order_id | total_qty | has_pear |
| --- | --- | --- |
| o1 | 4 | true |
| o2 | 6 | false |

To test whether an array contains a value, use `IN UNNEST`:

**posts tagged bigquery**

```sql
WITH posts AS (
  SELECT 1 AS post_id, ['sql', 'bigquery'] AS tags UNION ALL
  SELECT 2, ['python'] UNION ALL
  SELECT 3, ['bigquery', 'cost']
)
SELECT post_id
FROM posts
WHERE 'bigquery' IN UNNEST(tags)
ORDER BY post_id
```

Result in BigQuery (2 rows):

| post_id |
| --- |
| 1 |
| 3 |

Use `EXISTS (SELECT 1 FROM UNNEST(tags) AS tag WHERE tag LIKE 'big%')` when the test is more than equality.

## Unnesting multiplies the other columns

After a join with `UNNEST`, every column of the parent row is repeated once per element. Aggregating a parent column after unnesting counts it several times. Order `o1` has two items, so its amount of 100 is summed twice:

**the total should be 150**

```sql
WITH orders AS (
  SELECT 'o1' AS order_id, 100 AS amount, ['apple', 'pear'] AS items UNION ALL
  SELECT 'o2', 50, ['plum']
)
SELECT SUM(amount) AS total_amount
FROM orders, UNNEST(items) AS item
```

Result in BigQuery (1 row):

| total_amount |
| --- |
| 250 |

Aggregate parent columns before unnesting, or compute array values in a subquery as shown above.

## NULL and empty arrays

Unnesting a NULL array produces no rows, the same as an empty array:

**UNNEST of a NULL array**

```sql
SELECT COUNT(*) AS row_count
FROM UNNEST(CAST(NULL AS ARRAY<INT64>))
```

Result in BigQuery (1 row):

| row_count |
| --- |
| 0 |

BigQuery doesn't distinguish the two in results either: a NULL array column is returned as `[]`. See [NULL in BigQuery](https://docs.querylab.io/bigquery/null-handling/).

## What it costs

Each field of a repeated struct is stored as its own column, and `UNNEST` lets BigQuery read only the fields you use. The Google Analytics sample stores every hit of a session in a repeated `hits` struct with many nested fields. Reading the whole table:

**every column of one day of sessions**

```sql
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
```

BigQuery dry run: this query processes 15.9 MB.

Reading only the `hits` column costs nearly as much, 13.8 MB of the table's 15.9 MB:

**the whole hits array**

```sql
SELECT hits
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
```

BigQuery dry run: this query processes 13.8 MB.

Unnesting `hits` and reading one nested field costs 424.2 KB:

**one field of each hit**

```sql
SELECT hit.page.pagePath
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
  UNNEST(hits) AS hit
```

BigQuery dry run: this query processes 424.2 KB.

So unnesting doesn't make a query more expensive. Selecting the whole array or struct does. See [Nested and repeated data](https://docs.querylab.io/bigquery/nested-and-repeated-data/) and [How a query is billed](https://docs.querylab.io/bigquery/how-queries-are-billed/).

Turn an array into rows with UNNEST. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Query syntax, UNNEST operator](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator)

- [Work with arrays](https://cloud.google.com/bigquery/docs/arrays)