Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 38 ยท SQL Dialect and Types

Lists, Structs and Maps

Work with DuckDB nested data: LIST, ARRAY, STRUCT, MAP and UNION values, UNNEST, list comprehensions, lambda functions, list aggregates and dot access into nested columns.

Classic SQL wants flat tables: one value per cell. Real data is rarely flat. A user has several tags, an order has line items, a JSON event has a nested device object. DuckDB handles this natively with nested types, so one column can hold a list of values, a record with named fields, or a dictionary. You can then reshape, filter and aggregate inside those values with plain SQL.

Nested types are DuckDB-specific, and SQLite has none. DuckDB examples are read-only. The runnable stand-ins imitate lists and records with JSON in SQLite (json_array, json_object, json_each), which expresses the same ideas.

The five nested types

Type Meaning Example literal
LIST Ordered, any length, one element type [1, 2, 3]
ARRAY Ordered, fixed length (good for embeddings) [0.1, 0.2, 0.3]::DOUBLE[3]
STRUCT A record with named fields of any types {'name': 'Ana', 'age': 30}
MAP Key-value pairs, any number of keys MAP {'a': 1, 'b': 2}
UNION One of several types per row union_value(num := 5)

Types are written with brackets and braces: INTEGER[] is a list of integers, STRUCT(name VARCHAR, age INTEGER) is a struct, MAP(VARCHAR, INTEGER) is a map.

SQL
-- DuckDB (read-only example)
CREATE TABLE people (
  name   VARCHAR,
  tags   VARCHAR[],
  address STRUCT(city VARCHAR, zip VARCHAR)
);

INSERT INTO people VALUES
  ('Ana', ['admin', 'beta'], {'city': 'Lisbon', 'zip': '1000'}),
  ('Ben', [], {'city': 'Porto', 'zip': '4000'});

Working with lists

Index starting at 1, slice with [from:to], and use the list functions:

SQL
-- DuckDB (read-only example)
SELECT [10, 20, 30][1]         AS first,       -- 10
       [10, 20, 30][2:3]       AS slice,       -- [20, 30]
       len([10, 20, 30])       AS n,           -- 3
       list_contains(['a','b'], 'b') AS has_b, -- true
       list_sort([3,1,2])      AS sorted,      -- [1, 2, 3]
       [1,2] || [3]            AS joined;      -- [1, 2, 3]

Lambdas and comprehensions

You can transform or filter every element with a lambda (x -> expression) or a Python-style comprehension:

SQL
-- DuckDB (read-only example)
SELECT list_transform([1, 2, 3], x -> x * 10)           AS times_ten,   -- [10, 20, 30]
       list_filter([1, 2, 3, 4], x -> x % 2 = 0)        AS evens,       -- [2, 4]
       list_reduce([1, 2, 3, 4], (a, b) -> a + b)       AS total,       -- 10
       [x * x for x in [1, 2, 3] if x > 1]               AS squares;     -- [4, 9]

Building lists from rows: list() and array_agg

The reverse of unnesting is to collect rows into a list with the list() aggregate (array_agg is the same):

SQL
-- DuckDB (read-only example)
SELECT category_id, list(name ORDER BY price DESC) AS products
FROM products
GROUP BY category_id;

SQLite's counterpart is json_group_array. This runnable stand-in creates a list per category and shows its length:

SQL Stand-in: collect rows into a list ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

UNNEST: one row per element

UNNEST expands a list into separate rows, the key tool for normalizing nested data:

SQL
-- DuckDB (read-only example)
SELECT name, UNNEST(tags) AS tag FROM people;
-- Ana | admin
-- Ana | beta

Rows with an empty list produce no output rows, so Ben disappears. UNNEST also works on structs, expanding fields into columns: SELECT UNNEST(address) FROM people gives city and zip columns. Add recursive := true to flatten all levels.

SQLite's json_each is the equivalent table function:

SQL Stand-in: unnest a JSON list into rows

Press Run (or Ctrl + Enter) to see the result here.

Structs: records inside a column

Fields are reached with a dot, or with struct_extract. Many JSON and Parquet files produce structs automatically:

SQL
-- DuckDB (read-only example)
SELECT name, address.city, address['zip'] FROM people;

SELECT {'x': 1, 'y': 2} AS point;
SELECT struct_pack(id := 7, label := 'seven') AS record;

A struct has a fixed set of named fields, all rows have the same ones, and the field types are known, so a STRUCT column is stored as separate columns inside and stays fast. A frequent pattern is pulling a nested value out of event data: SELECT user.id, device.os FROM events.

Here is the JSON equivalent:

SQL Stand-in: reach into a JSON record

Press Run (or Ctrl + Enter) to see the result here.

Maps: dictionaries

A MAP holds any number of key-value pairs of the same key and value types, which is ideal for tags or attributes when the keys are not known in advance:

SQL
-- DuckDB (read-only example)
SELECT MAP {'color': 'red', 'size': 'L'} AS attrs;
SELECT map_keys(attrs), map_values(attrs), cardinality(attrs) FROM t;
SELECT element_at(attrs, 'color') FROM t;

Choose a struct when the field names are fixed and known (a point has x and y), and a map when keys vary by row (arbitrary labels).

Arrays and similarity

An ARRAY has a fixed size, for example FLOAT[384] for an embedding vector. Because the size is fixed, DuckDB can compute array_cosine_similarity(a, b) and array_distance(a, b) efficiently, which makes simple vector search possible without another database.

Combining nested types

Types nest freely: a list of structs is how you model an order with line items.

SQL
-- DuckDB (read-only example)
SELECT 1 AS order_id,
       [{'sku': 'duck', 'qty': 2}, {'sku': 'pond', 'qty': 1}] AS lines;

SELECT order_id, UNNEST(lines, recursive := true) FROM orders;   -- columns sku, qty
SELECT order_id, list_sum(list_transform(lines, l -> l.qty)) AS total_qty FROM orders;

When you read JSON or Parquet, DuckDB infers these nested types for you, and you do not need to write them by hand. See Reading JSON.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the index of the first element in a DuckDB list?

    DuckDB lists are 1-based, like standard SQL arrays.
  2. 2. What does UNNEST do?

    UNNEST expands a list so that each element becomes a separate row, repeating the other columns.
  3. 3. When should you prefer a STRUCT to a MAP?

    A struct has named fields of known types; a map suits flexible keys.
  4. 4. Which expression doubles every element of a list?

    list_transform applies a lambda to each element.
  5. 5. What does list(name) do in an aggregate query?

    The list aggregate (array_agg) gathers rows into a LIST value.

Summary

  • LIST, ARRAY, STRUCT, MAP and UNION let one column hold nested data.
  • Lists are 1-based; use list_transform, list_filter, list_reduce and comprehensions with lambdas.
  • UNNEST turns lists into rows; list() collects rows into a list.
  • Structs use dot access, maps suit variable keys, arrays suit fixed-size vectors.
  • Nested types compose, and JSON and Parquet readers create them automatically.

Next: Strings and regular expressions