Nested Types: LIST, STRUCT, MAP

Learning Focus

Use this lesson to work with nested data structures in DuckDB — a major advantage over traditional RDBMS systems.

Why Nested Types?

DuckDB natively supports nested types, making it easy to work with:

• JSON documents
• Arrays of values
• Key-value pairs
• Nested structs

This avoids the need to flatten all data into separate tables.

LIST Type

A LIST holds an ordered collection of values of the same type.

-- Create a table with a LIST column
CREATE TABLE student_scores (
    student_id  INTEGER,
    name        VARCHAR,
    test_scores INTEGER[]
);

INSERT INTO student_scores VALUES
    (1, 'Alice', [95, 87, 91, 88]),
    (2, 'Bob',   [72, 68, 75, 80]),
    (3, 'Carol', [99, 98, 97, 100]);

-- Query list elements (1-indexed)
SELECT name, test_scores[1] AS first_test FROM student_scores;

-- Get list length
SELECT name, len(test_scores) AS num_tests FROM student_scores;

-- Compute average over list
SELECT name, list_avg(test_scores) AS avg_score FROM student_scores;

-- Filter rows where any element satisfies condition
SELECT * FROM student_scores
WHERE list_contains(test_scores, 100);

STRUCT Type

A STRUCT holds named fields of potentially different types — like an inline record.

-- Create table with STRUCT column
CREATE TABLE employees (
    id      INTEGER,
    name    VARCHAR,
    address STRUCT(street VARCHAR, city VARCHAR, zip VARCHAR)
);

INSERT INTO employees VALUES
    (1, 'Alice', {'street': '123 Main St', 'city': 'Austin', 'zip': '78701'}),
    (2, 'Bob',   {'street': '456 Oak Ave', 'city': 'Denver', 'zip': '80201'});

-- Access struct fields
SELECT name, address.city AS city FROM employees;

-- Build a struct inline
SELECT {'name': 'Alice', 'age': 30} AS person;

MAP Type

A MAP holds dynamic key-value pairs where keys are of one type and values of another.

CREATE TABLE product_attrs (
    product_id INTEGER,
    attributes MAP(VARCHAR, VARCHAR)
);

INSERT INTO product_attrs VALUES
    (1, MAP {'color': 'red', 'size': 'large', 'material': 'cotton'}),
    (2, MAP {'color': 'blue', 'weight': '500g'});

-- Access map value by key
SELECT product_id, attributes['color'] AS color FROM product_attrs;

-- Get all keys
SELECT product_id, map_keys(attributes) AS keys FROM product_attrs;

-- Get all values
SELECT product_id, map_values(attributes) AS vals FROM product_attrs;

Working with JSON as Nested Types

-- Read JSON file with nested arrays
SELECT * FROM read_json_auto('orders.json');

-- Unnest a list into rows
SELECT name, unnest(test_scores) AS score
FROM student_scores;

-- Flatten struct fields
SELECT id, name, address.city, address.zip FROM employees;

List Functions Reference

SELECT list_avg([1,2,3,4,5]);             -- 3.0
SELECT list_sum([1,2,3,4,5]);             -- 15
SELECT list_min([1,2,3,4,5]);             -- 1
SELECT list_max([1,2,3,4,5]);             -- 5
SELECT list_contains([1,2,3], 2);         -- true
SELECT list_distinct([1,1,2,3,3]);        -- [1, 2, 3]
SELECT list_sort([3,1,2]);                -- [1, 2, 3]
SELECT list_slice([1,2,3,4,5], 2, 4);    -- [2, 3, 4]
SELECT flatten([[1,2],[3,4]]);            -- [1, 2, 3, 4]

Common Pitfalls

Pitfall	Consequence	Prevention
Using 0-based indexing on lists	Wrong element returned	DuckDB lists are 1-indexed
Querying MAP with missing keys	Returns NULL	Use coalesce(map['key'], 'default')
Joining on LIST columns	Unexpected behavior	Use unnest() first to normalize

Quick Reference

-- List
SELECT [1,2,3][1];                     -- 1
SELECT list_avg([10,20,30]);            -- 20.0
SELECT unnest([1,2,3]);                 -- 3 rows

-- Struct
SELECT {'a': 1, 'b': 2}.a;            -- 1

-- Map
SELECT MAP {'k': 'v'}['k'];            -- v
SELECT map_keys(MAP {'x': 1, 'y': 2}); -- [x, y]

What's Next

• Next: Basic SQL Operations - Apply your type knowledge in real queries.
• Module Overview - Return to this module index.