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.

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:

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

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):

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

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:

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

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:

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

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:

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

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 and 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:

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

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:

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

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:

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

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)]:

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

• A struct can’t be used in UNION DISTINCT, INTERSECT DISTINCT or EXCEPT DISTINCT. UNION ALL is fine:

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

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.

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:

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

costs 9.9 MB. Reading the whole author struct:

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

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:

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

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

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

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

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

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

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

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 holds: name the fields you need, and avoid SELECT * on tables with big nested columns.

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.

In Querylab.io the byte estimate 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).