TopolearnNested schemas are BigQuery's recommended way to model one-to-many data, avoiding costly joins.
A STRUCT is a record of named fields: STRUCT('Ana' AS name, 31 AS age). In a table schema it's a column of type RECORD with sub-fields, and you read them with dots: customer.address.city.
Combining the two gives nested and repeated fields: ARRAY<STRUCT<sku STRING, qty INT64, price NUMERIC>> stores an order's line items inside the order row. This is BigQuery's preferred way to model one-to-many relationships:
- No join at query time: the items are already next to their order.
- Storage is still columnar:
items.skuis stored as its own column, so a query that reads only SKUs doesn't read prices. - Data that's queried together stays together.
Query patterns:
- Fields of a non-repeated STRUCT:
SELECT customer.name. - Fields of a repeated STRUCT need UNNEST:
FROM orders o, UNNEST(o.items) AS iand theni.sku. - Aggregate inside a row without flattening:
(SELECT SUM(i.qty * i.price) FROM UNNEST(o.items) AS i) AS order_total. - Build nested output with
ARRAY_AGG(STRUCT(sku, qty)).
When to denormalize: event and order data with child records owned by one parent are ideal. Dimensions that are shared and change independently, like products or customers, usually stay separate tables. Very large or unbounded child lists are better as their own table. Remember the row size limit (100 MB) and that updating one nested element means rewriting the array.
Example
WITH orders AS (
SELECT 1 AS id,
STRUCT('Ana' AS name, 'Berlin' AS city) AS customer,
[STRUCT('pen' AS sku, 2 AS qty, NUMERIC '1.50' AS price),
STRUCT('ink', 1, NUMERIC '4.00')] AS items
)
SELECT
o.id,
o.customer.city AS city, -- Berlin
ARRAY_LENGTH(o.items) AS n_lines, -- 2
(SELECT SUM(i.qty * i.price) FROM UNNEST(o.items) AS i) AS total, -- 7.00
(SELECT i.sku FROM UNNEST(o.items) AS i
ORDER BY i.qty * i.price DESC LIMIT 1) AS top_sku -- ink
FROM orders AS o;
-- Rebuild a nested structure from flat rows
SELECT order_id, ARRAY_AGG(STRUCT(sku, qty) ORDER BY sku) AS items
FROM UNNEST([STRUCT(1 AS order_id, 'b' AS sku, 2 AS qty), STRUCT(1, 'a', 5)])
GROUP BY order_id;
-- 1 | [{sku: a, qty: 5}, {sku: b, qty: 2}]Remember
- STRUCT = record with named fields; dot to access
- ARRAY<STRUCT> stores child rows inside the parent, avoiding joins
- Correlated subqueries over UNNEST aggregate per row without flattening
Watch out for
- Normalizing event data into many joined tables as in an OLTP schema, then paying for big joins on every query.