BQBrains | Nested, Repeated, and JSON Data in BigQuery: STRUCT, ARRAY, and the JSON Type

Every BigQuery estate eventually collides with data that is not flat: an order with many line items, an event with a nested user object, a webhook payload whose shape changes weekly. BigQuery gives you three different tools for this — STRUCT, ARRAY, and the native JSON type — and choosing the wrong one is one of the most common causes of slow, expensive, hard-to-read warehouses we get called in to fix.

This tutorial covers all three: how to model with them, how to query them, when each is the right answer, and what they cost.

The three options in one paragraph

A STRUCT is a record with a fixed, declared set of typed fields — use it when the shape is known and stable. An ARRAY is a repeated value, optionally an array of structs — use it when a row genuinely has many children and you almost always read parent and children together. The JSON type stores semi-structured data whose schema you do not control, with no schema declaration at all, at the price of paying to parse it at query time. Flattening everything into separate tables joined by key is still a perfectly good fourth option, and often the right one.

Setup: a nested table you can query

The public GA4 sample dataset is the classic nested example, but let's build something small and self-contained so every query below is reproducible.

CREATE SCHEMA IF NOT EXISTS demo OPTIONS (location = 'US');

CREATE OR REPLACE TABLE demo.orders AS
SELECT * FROM UNNEST([
  STRUCT(
    1001 AS order_id,
    DATE '2026-03-02' AS order_date,
    STRUCT('C-77' AS customer_id, 'Acme GmbH' AS name, 'DE' AS country) AS customer,
    [STRUCT('SKU-1' AS sku, 2 AS qty, 19.99 AS unit_price),
     STRUCT('SKU-9' AS sku, 1 AS qty, 149.00 AS unit_price)] AS items
  ),
  STRUCT(
    1002,
    DATE '2026-03-03',
    STRUCT('C-12', 'Borealis Ltd', 'GB'),
    [STRUCT('SKU-1', 5, 19.99)]
  )
]);

The schema is one row per order, with the customer as a struct and the line items as an array of structs. SELECT * returns two rows, not three — the array stays packed inside its parent.

Reading structs: just use dots

SELECT order_id, customer.country, customer.name
FROM demo.orders;

Struct fields are addressed with dot notation and are physically stored as separate columns. That matters for cost: SELECT customer.country reads only that leaf column, not the whole struct. Structs are effectively free — they are namespacing over columnar storage, not a performance trade-off.

Reading arrays: UNNEST is a join

SELECT
  o.order_id,
  o.customer.name,
  i.sku,
  i.qty * i.unit_price AS line_total
FROM demo.orders AS o, UNNEST(o.items) AS i;

The comma is a CROSS JOIN against the array, so this returns one row per line item with the parent columns repeated — three rows for our two orders. Two rules save a lot of debugging:

Use LEFT JOIN UNNEST(...) when the array can be empty or NULL. A cross join silently drops parents with zero children, which is how "revenue is missing 4% of orders" bugs are born.
WITH OFFSET gives you the array position when order matters: UNNEST(o.items) AS i WITH OFFSET AS pos.

Aggregating back up to the parent is the mirror image:

SELECT
  order_id,
  (SELECT SUM(i.qty * i.unit_price) FROM UNNEST(items) AS i) AS order_total,
  ARRAY_LENGTH(items) AS line_count
FROM demo.orders;

A correlated subquery over the array is usually clearer — and faster — than unnesting, grouping, and re-joining, because BigQuery never has to shuffle the parent columns.

Building arrays with ARRAY_AGG

Going the other way, ARRAY_AGG(STRUCT(...)) collapses a flat table into a nested one, which is how you'd denormalise a staging table into a query-friendly mart:

CREATE OR REPLACE TABLE demo.orders_nested AS
SELECT
  order_id,
  ANY_VALUE(order_date) AS order_date,
  ARRAY_AGG(STRUCT(sku, qty, unit_price) ORDER BY sku) AS items
FROM demo.order_lines_flat
GROUP BY order_id;

Two cautions: arrays cannot contain NULL elements (filter with IGNORE NULLS), and an unbounded ARRAY_AGG on a skewed key can blow past the per-row size limit. If a parent can have hundreds of thousands of children, keep the children in their own table.

The native JSON type

For payloads whose schema you do not control, declare the column as JSON rather than STRING. BigQuery parses and stores it in a semi-structured columnar form, so field access does not re-parse the whole document.

CREATE OR REPLACE TABLE demo.events (
  event_id STRING,
  received_at TIMESTAMP,
  payload JSON
);

INSERT INTO demo.events VALUES
  ('e1', CURRENT_TIMESTAMP(),
   JSON '{"type":"signup","user":{"id":9,"plan":"pro"},"tags":["eu","trial"]}');

Access is with dot and bracket notation, then an extraction function to get a SQL scalar:

SELECT
  event_id,
  STRING(payload.type)                     AS event_type,
  INT64(payload.user.id)                   AS user_id,
  STRING(payload.user.plan)                AS plan,
  JSON_VALUE(payload, '$.user.plan')       AS plan_via_path,
  ARRAY_LENGTH(JSON_QUERY_ARRAY(payload, '$.tags')) AS tag_count
FROM demo.events;

Useful distinctions:

payload.user.plan returns a JSON value; STRING(...), INT64(...), BOOL(...) and friends convert it to a SQL type and error on a type mismatch. JSON_VALUE returns a STRING and returns NULL instead of erroring — safer for messy data.
JSON_QUERY_ARRAY gives you an array of JSON you can UNNEST; JSON_VALUE_ARRAY gives an array of STRING.
LAX_STRING, LAX_INT64 and the other LAX_ functions coerce loosely (the string "9" becomes 9), which is what you want over inconsistent producers.

Unnesting a JSON array works the same as any other array:

SELECT e.event_id, tag
FROM demo.events AS e,
UNNEST(JSON_QUERY_ARRAY(e.payload, '$.tags')) AS t,
UNNEST([STRING(t)]) AS tag;

Cost: what actually gets scanned

This is the part that decides architectures.

Pattern	Bytes billed on read
`SELECT s.leaf` on a STRUCT	Only that leaf column
`SELECT arr` (array of structs)	Only the referenced subfields of the array
`SELECT STRING(j.a.b)` on a JSON column	Only the accessed sub-column, once BigQuery's JSON encoding kicks in
`JSON_VALUE(str_col, '$.a.b')` on a STRING column	The entire string column, every query

That last row is the trap. A JSON blob kept in a STRING column costs full column scan on every single access, forever. If you have such a column and you query it more than occasionally, either convert it to JSON:

CREATE OR REPLACE TABLE demo.events_typed AS
SELECT event_id, received_at, SAFE.PARSE_JSON(raw_payload) AS payload
FROM demo.events_raw;

…or extract the two or three fields you actually filter on into real typed columns, and cluster on them. SAFE.PARSE_JSON returns NULL instead of failing on malformed rows; add wide_number_mode => 'round' if producers send numbers that exceed FLOAT64 precision.

You cannot partition or cluster on a JSON column, or on an array. Promote your partition and cluster keys — event date, tenant ID, event type — into top-level typed columns. That single habit is worth more than every other tip here.

Choosing between them

Fixed, known shape, read together? STRUCT. No downside.
One-to-many, read with the parent, bounded fan-out (tens to low thousands)? ARRAY of STRUCT. You trade UNNEST verbosity for eliminating a join and a shuffle.
Huge or unbounded fan-out, or children queried independently? Separate table, joined on key. Clustering both sides on the join key gets you most of the way to nested performance.
Schema you don't control, or a long tail of rarely-read fields? Native JSON, with hot fields promoted to typed columns.
Payload arriving as a string? Convert it. Almost always.

A quick migration recipe

If you inherited STRING payloads, do it in three steps rather than one big rewrite:

Add a JSON column and backfill with SAFE.PARSE_JSON, keeping the original for a release.
Create a view exposing the promoted typed columns (event_type, tenant_id, event_ts) plus the JSON remainder, and point consumers at the view.
Rebuild the physical table partitioned on the promoted timestamp and clustered on tenant and type, then drop the string column. Compare total_bytes_billed in INFORMATION_SCHEMA.JOBS before and after — a 10x reduction on dashboard queries is typical.

Nested and semi-structured modelling is where warehouse cost and warehouse clarity are usually won or lost. If you'd like a second pair of eyes on a schema — or a rescue on one that's already hurting — our data architecture consulting and cost optimization teams do this daily; get in touch.