SQL and Data Types

## 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.