# Nested and repeated data in BigQuery

> BigQuery stores child rows inside the parent row as a STRUCT or an ARRAY of STRUCTs, so related data needs no join. How to read, flatten and build nested data, the fan-out trap after UNNEST, and what each nested field costs.

Source: https://docs.querylab.io/bigquery/nested-and-repeated-data/

Updated: 2026-10-14

BigQuery lets one row hold its own child rows. A **nested** column is a `STRUCT` (shown as `RECORD` in the schema): a group of named fields in one column. A **repeated** column is an `ARRAY` (mode `REPEATED`): a list of values in one column. Put the two together and you get `ARRAY<STRUCT<...>>`, which is how an order holds its line items without a second table and a join.

Google's advice is to denormalize with these types when the data is hierarchical and read together, such as orders and their items. A join needs data from both tables to meet on the same workers, and a nested column is already stored with its parent row.

## What it looks like

An order with a shipping address and a list of items:

**a struct and an array of structs in one row**

```sql
WITH orders AS (
  SELECT 1001 AS order_id, STRUCT('Berlin' AS city, 'DE' AS country) AS ship_to, 5 AS shipping,
    [STRUCT('keyboard' AS sku, 1 AS qty, 49 AS price), ('mouse', 2, 20)] AS items
  UNION ALL SELECT 1002, ('Lyon', 'FR'), 8, [('monitor', 1, 199)]
  UNION ALL SELECT 1003, ('Oslo', 'NO'), 0,
    ARRAY<STRUCT<sku STRING, qty INT64, price INT64>>[]
)
SELECT order_id, ship_to, items
FROM orders
ORDER BY order_id
```

Result in BigQuery (3 rows):

| order_id | ship_to | items |
| --- | --- | --- |
| 1001 | {city: Berlin, country: DE} | [{sku: keyboard, qty: 1, price: 49}, {sku: mouse, qty: 2, price: 20}] |
| 1002 | {city: Lyon, country: FR} | [{sku: monitor, qty: 1, price: 199}] |
| 1003 | {city: Oslo, country: NO} | [] |

`ship_to` is a `STRUCT<city STRING, country STRING>`. `items` is an `ARRAY<STRUCT<sku STRING, qty INT64, price INT64>>`, with two elements for order 1001, one for 1002 and none for 1003. The other examples on this page use this data.

## Reading a struct and an array

A dot reaches into a struct. Square brackets pick one array element, either by `OFFSET` (counting from 0) or by `ORDINAL` (counting from 1):

**ship_to.city and items[OFFSET(0)]**

```sql
WITH orders AS (
  SELECT 1001 AS order_id, STRUCT('Berlin' AS city, 'DE' AS country) AS ship_to, 5 AS shipping,
    [STRUCT('keyboard' AS sku, 1 AS qty, 49 AS price), ('mouse', 2, 20)] AS items
  UNION ALL SELECT 1002, ('Lyon', 'FR'), 8, [('monitor', 1, 199)]
  UNION ALL SELECT 1003, ('Oslo', 'NO'), 0,
    ARRAY<STRUCT<sku STRING, qty INT64, price INT64>>[]
)
SELECT
  order_id,
  ship_to.city,
  items[OFFSET(0)].sku AS first_item,
  items[SAFE_OFFSET(1)].sku AS second_item
FROM orders
WHERE ARRAY_LENGTH(items) > 0
ORDER BY order_id
```

Result in BigQuery (2 rows):

| order_id | city | first_item | second_item |
| --- | --- | --- | --- |
| 1001 | Berlin | keyboard | mouse |
| 1002 | Lyon | monitor | NULL |

`OFFSET` fails when the position doesn't exist. `SAFE_OFFSET` returns `NULL` instead, which is what order 1002 gets for its second item. This is the error from `OFFSET`:

**OFFSET(1) on an array with one element**

```sql
WITH orders AS (
  SELECT 1001 AS order_id, STRUCT('Berlin' AS city, 'DE' AS country) AS ship_to, 5 AS shipping,
    [STRUCT('keyboard' AS sku, 1 AS qty, 49 AS price), ('mouse', 2, 20)] AS items
  UNION ALL SELECT 1002, ('Lyon', 'FR'), 8, [('monitor', 1, 199)]
  UNION ALL SELECT 1003, ('Oslo', 'NO'), 0,
    ARRAY<STRUCT<sku STRING, qty INT64, price INT64>>[]
)
SELECT order_id, items[OFFSET(1)].sku AS second_item
FROM orders
WHERE ARRAY_LENGTH(items) > 0
```

BigQuery rejects this query:

```text
Array index 1 is out of bounds (overflow)
```

## Flatten an array with UNNEST

To get one row per array element, put the array in `UNNEST` and join it to the row it came from. The comma is a `CROSS JOIN`:

**one row per item**

```sql
WITH orders AS (
  SELECT 1001 AS order_id, STRUCT('Berlin' AS city, 'DE' AS country) AS ship_to, 5 AS shipping,
    [STRUCT('keyboard' AS sku, 1 AS qty, 49 AS price), ('mouse', 2, 20)] AS items
  UNION ALL SELECT 1002, ('Lyon', 'FR'), 8, [('monitor', 1, 199)]
  UNION ALL SELECT 1003, ('Oslo', 'NO'), 0,
    ARRAY<STRUCT<sku STRING, qty INT64, price INT64>>[]
)
SELECT order_id, i.sku, i.qty * i.price AS line_total
FROM orders, UNNEST(items) AS i
ORDER BY order_id, i.sku
```

Result in BigQuery (3 rows):

| order_id | sku | line_total |
| --- | --- | --- |
| 1001 | keyboard | 49 |
| 1001 | mouse | 40 |
| 1002 | monitor | 199 |

Order 1003 is gone. An empty array unnests to no rows, and a cross join with no rows gives no rows. If you want the order kept, use `LEFT JOIN UNNEST`:

**LEFT JOIN UNNEST keeps the empty order**

```sql
WITH orders AS (
  SELECT 1001 AS order_id, STRUCT('Berlin' AS city, 'DE' AS country) AS ship_to, 5 AS shipping,
    [STRUCT('keyboard' AS sku, 1 AS qty, 49 AS price), ('mouse', 2, 20)] AS items
  UNION ALL SELECT 1002, ('Lyon', 'FR'), 8, [('monitor', 1, 199)]
  UNION ALL SELECT 1003, ('Oslo', 'NO'), 0,
    ARRAY<STRUCT<sku STRING, qty INT64, price INT64>>[]
)
SELECT order_id, i.sku, i.qty * i.price AS line_total
FROM orders LEFT JOIN UNNEST(items) AS i
ORDER BY order_id, i.sku
```

Result in BigQuery (4 rows):

| order_id | sku | line_total |
| --- | --- | --- |
| 1001 | keyboard | 49 |
| 1001 | mouse | 40 |
| 1002 | monitor | 199 |
| 1003 | NULL | NULL |

The `NULL` in the last row comes from the join. It doesn't mean the item exists with a missing SKU. More on arrays and `UNNEST` in [Arrays](https://docs.querylab.io/bigquery/arrays/) and [UNNEST](https://docs.querylab.io/bigquery/unnest/).

## The fan-out trap

After `UNNEST`, each parent column is repeated once for every element. Summing a parent column then counts it several times. Order 1001 has two items, so its shipping fee is added twice:

**SUM(shipping) before and after UNNEST**

```sql
WITH orders AS (
  SELECT 1001 AS order_id, STRUCT('Berlin' AS city, 'DE' AS country) AS ship_to, 5 AS shipping,
    [STRUCT('keyboard' AS sku, 1 AS qty, 49 AS price), ('mouse', 2, 20)] AS items
  UNION ALL SELECT 1002, ('Lyon', 'FR'), 8, [('monitor', 1, 199)]
  UNION ALL SELECT 1003, ('Oslo', 'NO'), 0,
    ARRAY<STRUCT<sku STRING, qty INT64, price INT64>>[]
)
SELECT
  (SELECT SUM(shipping) FROM orders) AS shipping_total,
  (SELECT SUM(shipping) FROM orders, UNNEST(items)) AS after_unnest
```

Result in BigQuery (1 row):

| shipping_total | after_unnest |
| --- | --- |
| 13 | 18 |

The right total is 13. After the `UNNEST` it is 18, because 1001 counts twice and 1003 not at all. A join between a parent table and a child table has the same problem, but here it is easier to miss because there is no `JOIN` keyword to remind you.

To total something inside each row, aggregate over the array in a subquery, without flattening the outer query:

**a subquery over the array**

```sql
WITH orders AS (
  SELECT 1001 AS order_id, STRUCT('Berlin' AS city, 'DE' AS country) AS ship_to, 5 AS shipping,
    [STRUCT('keyboard' AS sku, 1 AS qty, 49 AS price), ('mouse', 2, 20)] AS items
  UNION ALL SELECT 1002, ('Lyon', 'FR'), 8, [('monitor', 1, 199)]
  UNION ALL SELECT 1003, ('Oslo', 'NO'), 0,
    ARRAY<STRUCT<sku STRING, qty INT64, price INT64>>[]
)
SELECT
  order_id,
  shipping,
  (SELECT SUM(i.qty * i.price) FROM UNNEST(items) AS i) AS items_total
FROM orders
ORDER BY order_id
```

Result in BigQuery (3 rows):

| order_id | shipping | items_total |
| --- | --- | --- |
| 1001 | 5 | 89 |
| 1002 | 8 | 199 |
| 1003 | 0 | NULL |

Each order stays a single row, and 1003 gets `NULL` because `SUM` of no rows is `NULL`. Wrap it in `IFNULL(..., 0)` if you want zero.

## Building nested data

To go from flat rows to nested, group the child rows into an array of structs:

**ARRAY_AGG(STRUCT(...))**

```sql
WITH lines AS (
  SELECT 1001 AS order_id, 'mouse' AS sku, 2 AS qty UNION ALL
  SELECT 1001, 'keyboard', 1 UNION ALL
  SELECT 1002, 'monitor', 1
)
SELECT order_id, ARRAY_AGG(STRUCT(sku, qty) ORDER BY sku) AS items
FROM lines
GROUP BY order_id
ORDER BY order_id
```

Result in BigQuery (2 rows):

| order_id | items |
| --- | --- |
| 1001 | [{sku: keyboard, qty: 1}, {sku: mouse, qty: 2}] |
| 1002 | [{sku: monitor, qty: 1}] |

`ARRAY_AGG` takes an `ORDER BY` of its own, which fixes the element order inside each array. Without it the order is not defined. Google's documentation uses this pattern with `CREATE TABLE ... AS SELECT` to turn a normalized books-and-authors schema into one table.

## Limits

- An array can't contain another array. Wrap the inner array in a struct, `[STRUCT([1, 2] AS nums)]`:

**an array of arrays**

```sql
SELECT [[1, 2], [3]] AS number_groups
```

BigQuery rejects this query:

```text
Cannot construct array with element type ARRAY<INT64> because nested arrays are not supported at [1:9]
```

- A struct can't be used in `UNION DISTINCT`, `INTERSECT DISTINCT` or `EXCEPT DISTINCT`. `UNION ALL` is fine:

**a STRUCT in UNION DISTINCT**

```sql
SELECT STRUCT(1 AS id, 'a' AS tag) AS s
UNION DISTINCT
SELECT STRUCT(1 AS id, 'a' AS tag)
```

BigQuery rejects this query:

```text
Type STRUCT is not supported in UNION DISTINCT operation
```

`GROUP BY`, `=` and `SELECT DISTINCT` on a struct do work. Google's page on nested columns still says `SELECT DISTINCT` is incompatible with `RECORD`; in our test `SELECT DISTINCT s FROM UNNEST([STRUCT(1 AS a), STRUCT(1 AS a), STRUCT(2 AS a)]) AS s` returned two rows.

- A schema can have at most 15 levels of nested records.
- A table can have at most 10,000 columns, and each nested field counts as one.
- A row can be about 100 MB. That is a lot for a normal row, but a repeated field that grows without bound (every event ever seen for a user, appended to one row) will reach it.
- A partitioning or clustering column must be a top-level column. A field inside a struct can't be used. See [Partitioning](https://docs.querylab.io/bigquery/partitioning/).

## What a nested field costs

Each field inside a struct is stored as its own column, and a query pays only for the fields it names. `bigquery-public-data.github_repos.sample_commits` has an `author` struct with five fields and a repeated `difference` struct, one element per file changed. Reading one field of `author`:

**author.name**

```sql
SELECT author.name
FROM `bigquery-public-data.github_repos.sample_commits`
```

BigQuery dry run: this query processes 9.9 MB.

costs 9.9 MB. Reading the whole `author` struct:

**author**

```sql
SELECT author
FROM `bigquery-public-data.github_repos.sample_commits`
```

BigQuery dry run: this query processes 60.1 MB.

costs 60.1 MB, six times more, because it reads all five fields. The same holds for arrays of structs. The whole `difference` column is 1.7 GB:

**the whole difference array**

```sql
SELECT difference
FROM `bigquery-public-data.github_repos.sample_commits`
```

BigQuery dry run: this query processes 1.7 GB.

Unnesting it and reading one field reads one field of the array, 366 MB:

**UNNEST and one field**

```sql
SELECT d.new_path
FROM `bigquery-public-data.github_repos.sample_commits`, UNNEST(difference) AS d
```

BigQuery dry run: this query processes 365.7 MB.

Indexing one element of the array and reading one field gives the same 366 MB:

**difference[OFFSET(0)].new_path**

```sql
SELECT difference[OFFSET(0)].new_path AS first_path
FROM `bigquery-public-data.github_repos.sample_commits`
```

BigQuery dry run: this query processes 365.7 MB.

Counting the elements is different. `ARRAY_LENGTH` needs every element, so it reads the whole array:

**ARRAY_LENGTH(difference)**

```sql
SELECT ARRAY_LENGTH(difference) AS files_changed
FROM `bigquery-public-data.github_repos.sample_commits`
```

BigQuery dry run: this query processes 1.7 GB.

That is the same 1.7 GB as selecting the column, so asking for the length of an array is not a cheap question. The practical rule from [how queries are billed](https://docs.querylab.io/bigquery/how-queries-are-billed/) holds: name the fields you need, and avoid `SELECT *` on tables with big nested columns.

In Querylab.io the [byte estimate](https://docs.querylab.io/writing-sql/cost-limits/#what-gets-estimated) updates as you type, so you can compare `author` with `author.name` before you run either. The Schema tab of a table lists each `STRUCT` and `ARRAY` field as a `parent.child` row that you can search ([table information](https://docs.querylab.io/schema-browser/table-information/#schema)).

## When not to nest

Nesting suits data that belongs to one parent and is read with it. It is a poor fit for data that many parents share, such as a product catalog referenced by every order. Copying it into every row repeats it everywhere and makes a change to it a rewrite of every row. Keep shared tables separate and join them.

Read a repeated field with dot access and OFFSET. 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:

- [Specify nested and repeated columns in table schemas](https://cloud.google.com/bigquery/docs/nested-repeated)

- [Use nested and repeated fields](https://cloud.google.com/bigquery/docs/best-practices-performance-nested)

- [Quotas and limits](https://cloud.google.com/bigquery/quotas)