Reference

SQL Structs and Maps

A STRUCT is a fixed set of named fields in one value, like a row in a column. A MAP is a set of keys and values that can differ from row to row.

A struct packs a few named fields, each with its own type, into one value: {'name': 'Trowel', 'price': 9.5}. Every row of a struct column has the same fields. A map holds pairs of keys and values where the keys are data, so one row can have three keys and the next one none.

A struct per product

select product_id,
       {'name': product_name, 'price': price, 'in_stock': quantity_on_hand} as item
from products
order by product_id
limit 3

Practice

Practice keeping a list of structs per customer

Structs

Form	Gives
`{'a': 1, 'b': 'x'}`	A struct literal; field names in single quotes
`struct_pack(a := 1, b := 'x')`	The same struct, written as a function
`row(1, 'x')`	A struct with unnamed fields, shown as `(1, 'x')`
`s.a`, `s['a']`	One field
`s.*`, `unnest(s)`	Every field as its own column
`struct_insert(s, c := 3)`	The struct with one more field
`list({'title': t, 'on': d} order by d)`	A list of structs: several columns kept together for each item in a group

Dot and bracket access

with items as (
  select {'name': product_name, 'price': price} as item
  from products
  where category_id = 3
)
select item.name,
       item['price'] as price
from items

Asking for a field the struct doesn't have stops the query with Could not find key "cost" in struct, followed by a field it does have as a suggestion. That's a check at the time the query is planned, so a typo in a field name fails loudly instead of returning NULLs.

A field that isn't there

select {'name': product_name, 'price': price}.cost
from products

Comparing structs

Two structs are equal when every field is equal. They sort field by field, first to last, like a multi-column ORDER BY. So max of a struct whose first field is the price returns the priciest row's whole struct:

The priciest product per category, with its name

select category_id,
       max({'price': price, 'name': product_name}) as priciest
from products
group by category_id
order by category_id

Houseplants returns Fiddle-Leaf Fig with its price of 32. When two products tie on price, the next field, the name, decides. arg_max(product_name, price) gives the name alone.

Maps

Form	Gives
`MAP {'size': 'L', 'color': 'green'}`	A map literal
`map(keys_list, values_list)`	A map from two lists of the same length
`m['size']`	The value for a key, or `NULL` if the key is absent
`map_extract(m, 'size')`	The same value, wrapped in a list: `['L']`
`map_keys(m)`, `map_values(m)`	The keys, or the values, as a list
`map_contains(m, 'size')`	True if the key is present
`cardinality(m)`	The number of keys
`map_from_entries([('a', 1), ('b', 2)])`	A map from a list of key and value pairs

Category names by id, as a map

with lookup as (
  select map(list(category_id order by category_id),
             list(category_name order by category_id)) as names
  from categories
)
select names[3]               as category_3,
       names[99]              as missing,
       cardinality(names)     as categories,
       map_contains(names, 8) as has_8
from lookup

Key 3 is Tools, key 99 is NULL, and there are 8 keys. Every key in a map must be unique: map(['a', 'a'], [1, 2]) fails with Map keys must be unique.

Struct or map?

Use a struct when you know the fields in advance and every row has them, such as a product's name and price. You get a type for each field and an error on a misspelled name.
Use a map when the keys are data, such as product attributes that differ by category or counts per tag. Every value shares one type, and an absent key is NULL, not an error.
Use JSON when the shape isn't known until you read it, or the data came from somewhere else. See the JSON reference.

How other databases differ

Engine	Struct	Map
DuckDB	`STRUCT`, `{'a': 1}`	`MAP`
PostgreSQL	Composite types: `row(1, 'x')`, with named fields after `CREATE TYPE`	`hstore` (text only) or `jsonb`
BigQuery	`STRUCT`, read with a dot	None; an array of key and value structs, or JSON
Snowflake	`OBJECT`, read with `obj:field`	`OBJECT` or `MAP`
Spark SQL, Hive	`STRUCT`	`MAP`
MySQL, SQL Server, SQLite	A JSON object

Dialect note

Single quotes around field names are DuckDB's literal syntax. {'a': 1} is a struct in DuckDB but a syntax error in most other engines, and in JSON the same braces need double quotes: {"a": 1}.

Lesson

SQL List Practice: list(), Indexing, and list_contains

Collect each session's pages into one ordered list.

Lesson

SQL UNNEST Practice: Turn a List Into Rows

Unnest gift bundles and price them against products.

Lesson

SQL Struct Practice: Named Fields in One Value

Keep each customer's rental history as a list of structs.

Reference

SQL List Functions

list(), indexing, lambdas, and unnest back into rows.