In BigQuery, what do ARRAY and STRUCT columns store, and how do you read values inside them?
answer
- one row can hold a whole sub-table
- dot notation stops at a repeated field
- a table function turns elements into rows
- ARRAY_AGG(STRUCT(...)) goes the other way
- WITH OFFSET recovers element position
basics
~20 sA STRUCT is a record of named, typed fields you address with dot notation. An ARRAY is a repeated field holding an ordered list of values in one row; to treat its elements as rows you must UNNEST it.
solid answer
~40 sBigQuery schemas are trees, not flat rows. A `STRUCT` (shown as type RECORD in the schema UI) groups named fields, read with dot notation: `SELECT customer.city FROM orders`. An `ARRAY` is a repeated field — an ordered list of values of one type — and you cannot reach inside it with a dot. `SELECT items.sku` fails when `items` is `ARRAY<STRUCT<sku STRING, qty INT64>>`; you flatten it first with the table function `UNNEST`, usually correlated to the row: `FROM orders AS o, UNNEST(o.items) AS item` gives one output row per array element, with the parent columns repeated. `UNNEST(...) WITH OFFSET AS pos` also returns each element's zero-based position. The reverse direction is `ARRAY_AGG(STRUCT(...))` under a `GROUP BY`, which rebuilds a repeated field from flat rows.
go deeper
Be ready to say what STRUCT and ARRAY hold, read a struct field with dot notation, and flatten an array with UNNEST correlated to its parent row.
Explain that each leaf field is stored as its own column, that the comma before UNNEST is a cross join, and how ARRAY_AGG(STRUCT(...)) rebuilds a repeated field.
Show judgment about when to flatten versus aggregate the array in place with a scalar subquery, and know that bytes scanned follow the leaf columns referenced, not the row count emitted.
Own the schema-shape decision: which entities become repeated fields, how deep the nesting goes, and what that costs downstream consumers who expect flat rows.
## Why BigQuery has nested types at all A BigQuery table schema is a tree rather than a flat list of scalar columns. Two composite types build that tree. A `STRUCT` (the schema browser and the REST API call it a RECORD) is a container of named, typed fields — the analytical equivalent of an inlined one-to-one row. An `ARRAY` is a *repeated* field: an ordered list of values, all of the same type. Combining them, `ARRAY<STRUCT<...>>`, lets a single row carry an entire sub-table — every line item of an order, every hit in a session — instead of pushing those children into a separate table that must be joined back. This is the idiomatic BigQuery modelling move, and it exists because the storage engine keeps each *leaf* field as its own column. Referencing `items.sku` reads only the SKU leaf; the quantities and prices are never touched. So nesting does not force you to read a fat blob, and it removes a join from the hot path. ## Reading a STRUCT Dot notation, arbitrarily deep: ```sql SELECT order_id, customer.address.city FROM orders; ``` A `STRUCT` value can also be selected whole (`SELECT customer FROM orders`), in which case the client receives a nested record. `SELECT customer.*` expands its fields into separate output columns. ## Reading an ARRAY Dot notation does **not** work through a repeated field. Given `items ARRAY<STRUCT<sku STRING, qty INT64>>`, the statement `SELECT items.sku FROM orders` is an error — there are many `sku` values per row, and a SQL column position holds one value. You have two idioms. **Flatten it.** `UNNEST` is a table-valued function that turns an array into a table of one row per element. Correlated to the parent row it reads as a join: ```sql SELECT o.order_id, item.sku, item.qty FROM orders AS o, UNNEST(o.items) AS item; ``` The comma is a `CROSS JOIN`. Parent columns repeat once per element, so the result has one row per line item. `UNNEST(o.items) AS item WITH OFFSET AS pos` additionally exposes the element's zero-based index, which is the only reliable way to recover array order after flattening. **Or keep it as an array and aggregate it in place.** A scalar subquery over `UNNEST` computes a per-row answer without multiplying rows: ```sql SELECT order_id, ARRAY_LENGTH(items) AS line_count, (SELECT SUM(i.qty) FROM UNNEST(items) AS i) AS units FROM orders; ``` `EXISTS (SELECT 1 FROM UNNEST(items) AS i WHERE i.sku = 'A1')` filters parents by array content the same way. ## Going the other direction `ARRAY_AGG` builds a repeated field from flat rows, and `STRUCT(...)` packs several columns into each element: ```sql SELECT order_id, ARRAY_AGG(STRUCT(sku, qty) ORDER BY line_no) AS items FROM order_lines GROUP BY order_id; ``` `ARRAY_AGG` accepts `DISTINCT`, `ORDER BY`, `LIMIT`, and `IGNORE NULLS` — the last matters because `ARRAY_AGG` raises an error if the input contains NULLs and you have not said to ignore them. `ARRAY(SELECT AS STRUCT ...)` is the subquery form of the same construction. ## Rules worth memorising - Arrays are ordered, and that order is preserved in storage; `WITH OFFSET` is how you read it back. - BigQuery has no direct `ARRAY<ARRAY<T>>`. Wrap the inner array in a struct: `ARRAY<STRUCT<x ARRAY<INT64>>>`. - A repeated field is empty rather than absent; `ARRAY_LENGTH(items) = 0` is the test for "no children", not `items IS NULL`. - Array element types must be uniform. Genuinely heterogeneous payloads belong in the `JSON` type instead. - `UNNEST` also works on array literals and parameters, which is why `WHERE id IN UNNEST(@ids)` is the standard way to pass a list into a parameterised query. ## Cost intuition Bytes scanned in BigQuery are a function of the *columns referenced*, not of how many rows a query emits. `UNNEST` is a reshaping operator: flattening a 20-element array does not scan twenty times the data, and a query that touches only `items.sku` pays only for the SKU leaf. What flattening does change is the row count flowing through the rest of the plan — which is where the double-counting and row-loss traps around `UNNEST` come from.
- How do you get a per-row count of array elements without flattening the table?Use `ARRAY_LENGTH(items)`, or a scalar subquery over the array such as `(SELECT COUNT(*) FROM UNNEST(items))`. Both stay at parent-row granularity, so the query still returns one row per order. Flattening with `UNNEST` and then `COUNT(*)` would work too but changes the shape of the result and silently drops orders with empty arrays.
- Why does BigQuery reject a column typed ARRAY<ARRAY<INT64>>, and what do you write instead?BigQuery does not allow an array directly inside another array. Wrap the inner array in a struct: `ARRAY<STRUCT<values ARRAY<INT64>>>`. The struct gives the inner list a named field to hang off, and you flatten it with two levels of `UNNEST` — one over the outer array, one over the inner field.
- How do you test whether any element of a repeated field matches a value?Use `EXISTS (SELECT 1 FROM UNNEST(items) AS i WHERE i.sku = 'A1')`, or for a scalar array `'A1' IN UNNEST(skus)`. Both stay at one row per parent. Flattening with a comma join and filtering in `WHERE` also finds the matches, but it duplicates the parent row once per matching element.
saying these in an interview costs you the question
- Thinks items.sku works when items is a repeated field
- Calls UNNEST expensive because it scans the array many times
- Uses items IS NULL to test for an empty repeated field
- Assumes array order is lost after flattening
- Believes STRUCT fields are stored as one opaque blob