Nested Data

A nested column holds a structure where a flat column holds a single value. An order row in a flat schema stores customer_id, order_date and total, 1 value per column. The same order in a nested schema can also carry items, a list of 3 records, each with a sku, a quantity and a price. The list sits inside the row, so reading the order reads its items without a join.

Order 1001 occupies 1 row in orders and 3 rows in order_items, linked by order_id. Splitting a repeating part into its own table like this, 1 row per item, is called normalizing. The embedded version keeps the same facts in 1 table:

Both versions answer every question about these orders. They differ in what a row means and therefore in which queries are cheap. In the normalized version, a row of order_items means 1 item of 1 order, so counting how many times TEE-M sold is a filter and a sum over 1 table. In the embedded version, a row means 1 complete order, so loading an order for the checkout page is 1 row read, and counting TEE-M sales means opening every list.

Run the Airbnb listing through those 5 questions. Amenities exist only for the listing, load with it, change with it, stay small and belong to 1 listing: embed. Reviews are written and edited on their own, grow without a ceiling and are read in bulk by the ratings pipeline: normalize. The host is shared across listings, so the listing stores a host_id and the host table holds the facts, with at most a display name copied in for the search card.

A struct is 1 column holding a fixed set of named, typed fields. The field names and their types live in the table schema, the same as top-level columns, so the engine knows before reading a single row that shipping_address has a city of type VARCHAR. In the SQL used here the type is spelled ROW; the idea is the one most people call a struct.

The same 4 facts could live in shipping_street, shipping_city, shipping_postal_code and shipping_country. The struct wins on 3 counts. The grouping is in the schema, so a reader sees 1 address rather than 4 columns that happen to share a prefix. Absence is 1 check: shipping_address IS NULL says the order has no address, where the prefixed version needs 4 checks and a rule about which columns may be missing on their own. And the shape is reusable: billing_address can be declared with the same ROW(...) and compared field by field with shipping_address in a single expression.

A separate addresses table loses here too. Every order has exactly 1 shipping address, it is read whenever the order is read, and it is a snapshot that never changes after shipment. A join would cost a lookup per order and buy nothing, since none of the 5 questions from the previous section point toward a table.

A field read through a NULL struct is NULL, so order 1003 reports both. A pickup order could hold a struct with country set and city NULL, and would report no_address = false with no_city = true: a partial address, a different state from no address. The struct also separates no address from an address whose every field is NULL, where 4 flat columns, all NULL, could mean either one.

Fields can themselves be structs. Adding geo ROW(lat DOUBLE, lon DOUBLE) inside shipping_address gives shipping_address.geo.lat, and the same NULL rule applies at every level. Building a value by hand uses ROW(...) with a cast to name the fields:

The guest's dishwasher filter runs on the array in place, with WHERE contains(amenities, 'dishwasher'). A question across listings, such as which amenities are most common, wants 1 row per amenity instead. UNNEST produces exactly that, 1 output row per element, and the CROSS JOIN pairs each element with its parent listing:

The photos array is ordered because the host chose which picture comes first. Unnesting an array of structs spreads each struct's fields into columns, and WITH ORDINALITY adds the position so the order survives the trip to rows:

Position 1 is the cover photo. Had the photos lived in a separate table, that fact would need a sort_order column, an ORDER BY on every read, and a uniqueness rule on (listing_id, sort_order) to keep 2 photos off position 1. The array gives all 3 by construction.

A map is 1 column holding key-value pairs where each row chooses its own keys. All keys share a type and all values share a type, and the schema says nothing about which keys will appear. That makes it the shape for attributes that vary from record to record: a product catalog where a laptop has ram_gb and screen_in, a shirt has size and color, and a mug has capacity_ml. A struct would need a field for every attribute any product might have, hundreds of fields, nearly all NULL on every row.

Looking up a key uses element_at, which returns NULL when the key is absent, the same way it does for arrays. The subscript form attributes['color'] raises an error on product 7, where no such key exists, and takes the query down with it.

Filtering on a key is the same call in a WHERE clause: WHERE element_at(attributes, 'size') = 'M' returns the shirt. Unnesting a map produces 1 row per pair, with the key and the value as 2 columns, which turns the question of which keys exist at all into an aggregate:

The flexibility costs 3 guarantees. First, type. Every value in MAP(VARCHAR, VARCHAR) is text, so ram_gb is stored as '16', and element_at(attributes, 'ram_gb') > '8' compares 2 strings, where '16' sorts before '8' because '1' comes before '8'. The laptop fails the filter. Correct code casts every time: CAST(element_at(attributes, 'ram_gb') AS INTEGER) > 8. Second, the key set. Nothing stops 1 upstream team writing color and another writing colour, and both land in the table. Third, visibility. A reader of the schema learns that attributes exist and nothing about what they are, so documentation has to carry what the schema would otherwise say.

A map whose values are numbers, dates and words all stored as text is a sign that the common fields want a struct, with the map kept for the rest.

Row storage writes each row's values next to each other, so reading 1 column means reading every row in full. Columnar storage writes each column's values together, in chunks, with the minimum and maximum of each chunk recorded in its footer. A query that touches 2 of 40 columns reads 2 streams, and a filter such as WHERE country = 'PT' skips every chunk whose min and max rule it out. Most analytical tables are stored this way, and nested columns get the same treatment, with a twist.

The consequences follow from the table. WHERE location.city = 'Lisbon' reads the location.city stream and nothing else, and its chunk statistics let the engine skip chunks that hold no Lisbon. Counting photos with CROSS JOIN UNNEST(photos) reads at most the photos.url stream, since the list markers beside it say where each listing's photos begin and end. Reading a field of a struct costs the same as reading a top-level column of the same type.

A map has only 2 leaf streams, keys and values, shared by every key that will ever appear. To evaluate element_at(attributes, 'color') the engine reads the entire key stream, scans each row's keys for a match, and pulls the matching position from the value stream. The chunk statistics on the value stream cover ram sizes, shirt sizes and colors mixed together, so a filter on one key skips nothing. A struct field has its own stream and its own statistics, which is the whole difference.

This is why the monthly key-frequency query from the previous section matters. A key like pet_policy that sits on 80 percent of listings and appears in the search filter has outgrown the map. Promoting it to a top-level column gives it a stream and statistics, and the map keeps the long tail of rare keys where flexibility earns its cost.

A VARCHAR column holding JSON looks nested and stores as 1 stream of text. To answer json_extract_scalar(payload, '$.location.city') the engine reads every byte of every payload and parses it row by row, work that grows with the size of the whole document rather than the field asked for. Typed nested columns hand the engine the leaves; JSON text makes it dig for them on each query. A raw landing table can hold JSON text, and the first transformation should cast it into declared structs, arrays and maps.