Nested and Repeated Fields in BigQuery

Relational habit says one fact per row: orders in one table, their line items in another, and a join to put them back together. BigQuery supports that. It also supports another shape, which is to keep the line items inside the order's own row. That is what nested and repeated fields are, and used well they remove a class of errors as well as a join.

Two ideas: nesting and repetition

• Nested means a column that is itself a group of named fields. In SQL this is a STRUCT; in a schema it is a column of type RECORD.
• Repeated means a column that holds several values in one row. In SQL this is an ARRAY; in a schema it is a column with mode REPEATED.

Combined, they give an array of structs: many child records inside one parent row (Google Cloud: nested and repeated columns). One order in newline-delimited JSON looks like this:

{"order_id": 1, "customer_id": 2, "total": 1.98,
 "lines": [{"track_id": 2, "unit_price": 0.99, "quantity": 1},
           {"track_id": 4, "unit_price": 0.99, "quantity": 1}]}

and the table that holds it is declared like this:

CREATE TABLE sales.orders (
  order_id     INT64,
  customer_id  INT64,
  total        NUMERIC,
  lines ARRAY<STRUCT<
    track_id   INT64,
    unit_price NUMERIC,
    quantity   INT64>>
);

Why BigQuery encourages it

Google's guidance is direct: the recommended way to denormalize data in BigQuery is with nested and repeated fields, and it suits relationships that are hierarchical and usually queried together, such as an order and its lines (Google Cloud: use nested and repeated fields).

The reason is how a distributed engine runs a join. Matching rows from two large tables means moving data between workers. Rows that already contain their children need no such movement. The same page adds a caution: a star schema is already well suited to analytics, and nesting it further may not help.

Building and querying one, on real data

The script below builds a nested orders table from the Chinook sample database (Chinook on GitHub), which has 412 invoices and 2,240 invoice lines, and then queries it. Every statement is GoogleSQL. Each is parsed with the BigQuery grammar of the sqlglot library, translated to DuckDB and run locally. It is not a run of BigQuery.

# GoogleSQL for nested and repeated fields, on the Chinook sample database. Each statement is parsed
# with sqlglot's BigQuery grammar, translated to DuckDB and run locally. It is not a run of BigQuery.
import sqlite3
import duckdb
import pandas as pd
import sqlglot

source = sqlite3.connect("../../sqlds/Chinook_Sqlite.sqlite")
con = duckdb.connect()
for table in ["Invoice", "InvoiceLine"]:
    frame = pd.read_sql_query(f"SELECT * FROM {table}", source)
    con.execute(f"CREATE TABLE {table} AS SELECT * FROM frame")


def bq(label, sql):
    result = con.execute(sqlglot.transpile(sql, read="bigquery", write="duckdb")[0])
    if label:
        print(f"{label:36} {result.fetchall()}")


# One row per order, with its line items held inside the row as an array of structs.
bq(None, """
CREATE TABLE orders AS
SELECT i.InvoiceId AS order_id, i.CustomerId AS customer_id, i.Total AS total,
       ARRAY_AGG(STRUCT(l.TrackId AS track_id, l.UnitPrice AS unit_price, l.Quantity AS quantity)
                 ORDER BY l.InvoiceLineId) AS lines
FROM Invoice AS i
JOIN InvoiceLine AS l ON l.InvoiceId = i.InvoiceId
GROUP BY i.InvoiceId, i.CustomerId, i.Total""")

bq("orders, line items, most lines", """
SELECT COUNT(*), SUM(ARRAY_LENGTH(lines)), MAX(ARRAY_LENGTH(lines)) FROM orders""")

bq("first line of orders 1 to 3", """
SELECT order_id, lines[OFFSET(0)].track_id, ARRAY_LENGTH(lines)
FROM orders ORDER BY order_id LIMIT 3""")

bq("revenue summed over orders", """
SELECT ROUND(SUM(total), 2) FROM orders""")

bq("same total after a flat join", """
SELECT ROUND(SUM(i.Total), 2)
FROM Invoice AS i JOIN InvoiceLine AS l ON l.InvoiceId = i.InvoiceId""")

bq("flattened: rows, revenue from lines", """
SELECT COUNT(*), ROUND(SUM(line.unit_price * line.quantity), 2)
FROM orders AS o, UNNEST(o.lines) AS line""")

bq("largest orders, total against lines", """
SELECT order_id, total,
       (SELECT ROUND(SUM(l.unit_price * l.quantity), 2) FROM UNNEST(lines) AS l)
FROM orders ORDER BY total DESC, order_id LIMIT 3""")

bq("orders containing track 2", """
SELECT COUNT(*) FROM orders
WHERE EXISTS (SELECT 1 FROM UNNEST(lines) AS l WHERE l.track_id = 2)""")

orders, line items, most lines       [(412, 2240, 14)]
first line of orders 1 to 3          [(1, 2, 2), (2, 6, 4), (3, 16, 6)]
revenue summed over orders           [(2328.6,)]
same total after a flat join         [(20848.62,)]
flattened: rows, revenue from lines  [(2240, 2328.6)]
largest orders, total against lines  [(404, 25.86, 25.86), (299, 23.86, 23.86), (96, 21.86, 21.86)]
orders containing track 2            [(2,)]

Line by line:

• 412 rows hold 2,240 line items. ARRAY_AGG(STRUCT(...)) gathers each order's lines into its row. ARRAY_LENGTH counts them; the largest order has 14.
• Fields are reached with dots and offsets. lines[OFFSET(0)].track_id is the track on the first line of an order.
• The total is right without thinking. SUM(total) over the nested table gives 2,328.60, because each order is one row.
• The flat join is wrong by a factor of nearly nine. Joining orders to lines and summing the order total gives 20,848.62. Each order's total has been repeated once per line. This is the fan-out error, one of the commonest mistakes in analytics, and the nested shape makes it impossible at the order level.
• Flattening goes down to line level when you need it. UNNEST gives 2,240 rows, and revenue computed from the lines agrees with the order totals.
• A subquery can read the array without flattening the table. The sixth query computes each order's value from its own lines and returns one row per order.
• An existence test answers "which orders contain this item". With EXISTS over the array, two orders include track 2.

The UNNEST rules worth knowing

UNNEST turns an array into rows. Joined back to its own row, it flattens the table (Google Cloud: work with arrays). Four rules explain most surprises:

1. A comma is a join. FROM orders AS o, UNNEST(o.lines) AS line is the same as an explicit CROSS JOIN or INNER JOIN.
2. Empty arrays vanish. An inner join drops parent rows whose array is empty or NULL. Use LEFT JOIN to keep them.
3. Order is not preserved. Add WITH OFFSET if the position of each element matters.
4. Two arrays multiply. Flattening two arrays of the same row produces every combination of their elements. Flatten one at a time.

-- Keep orders that have no lines, and number each line
SELECT o.order_id, line.track_id, position
FROM sales.orders AS o
LEFT JOIN UNNEST(o.lines) AS line WITH OFFSET AS position
ORDER BY o.order_id, position;

When not to nest

Nesting is a choice about which questions are easy. It favours questions about the parent. Keep a separate table when:

• The children are numerous or unbounded. Google's wording is that a repeated field suits a one-to-many relationship as long as it is not high cardinality. A customer's every click does not belong inside the customer row, and a row cannot exceed roughly 100 MB (Google Cloud: quotas and limits).
• The children are mostly queried alone. If most reports are about line items, every query pays for an UNNEST.
• The relationship is many-to-many. Products belong to many orders; nesting products inside orders duplicates them.
• The children change independently. Updating one element means rewriting the array that contains it.
• A star schema already serves the workload.

Limits to design around

• A schema can nest records at most 15 levels deep.
• An array cannot directly contain another array. Wrap the inner array in a struct.
• A RECORD column cannot be used with SELECT DISTINCT, UNION, INTERSECT or EXCEPT DISTINCT. Select the scalar fields you need instead.
• Clustering columns must be top-level and non-repeated (Google Cloud: clustered tables). You cannot cluster on a field inside an array.

A sensible default

Nest what belongs to the parent and is read with it: lines in an order, addresses of a person, steps of a session. Keep as separate tables the things that have a life of their own: customers, products, calendars. Most well-designed BigQuery tables are a star schema whose fact table carries one or two nested arrays.

Related reading: partitioning and clustering for the other half of table design, window functions for analysis across rows, and BigQuery explained for the engine underneath.

Data modelling for warehouses is covered in the Data Engineering course.

Sources

• Specify nested and repeated columns in table schemas (Google Cloud Documentation) — Google Cloud
• Use nested and repeated fields (Google Cloud Documentation) — Google Cloud
• Work with arrays (Google Cloud Documentation) — Google Cloud
• Quotas and limits (Google Cloud Documentation) — Google Cloud
• Introduction to clustered tables (Google Cloud Documentation) — Google Cloud
• Chinook sample database (GitHub) — GitHub