DuckDB LIST Functions Complete Guide: From UNNEST to Aggregation

CREATE TABLE orders AS
SELECT * FROM (VALUES
    (1, 'Alice',   ['laptop', 'mouse', 'keyboard'], [5000, 150, 350]),
    (2, 'Bob',     ['phone'],                         [8000]),
    (3, 'Carol',   ['tablet', 'charger'],             [2000, 200]),
    (4, 'Dave',    ['laptop', 'monitor', 'webcam'],   [5000, 2800, 400]),
    (5, 'Eve',     ['headphones', 'mouse'],           [600, 150])
) AS t(order_id, customer, items, prices);

SELECT 
    o.order_id,
    o.customer,
    u.item
FROM orders AS o
CROSS JOIN UNNEST(o.items) AS u(item);

SELECT 
    o.order_id,
    u.item,
    p.price
FROM orders AS o
CROSS JOIN UNNEST(o.items) AS u(item)
CROSS JOIN UNNEST(o.prices) AS p(price);

SELECT 
    customer,
    array_agg(item) AS all_items,
    list_distinct(array_agg(item)) AS unique_items,
    list_count(array_agg(item)) AS total_count
FROM (
    SELECT o.order_id, o.customer, u.item
    FROM orders AS o
    CROSS JOIN UNNEST(o.items) AS u(item)
) AS flattened
GROUP BY customer;

SELECT list_transform(
    ['  Hello ', 'WORLD  ', '  DuckDB  '],
    x -> trim(lower(x))
) AS cleaned;
-- Result: ['hello', 'world', 'duckdb']

-- Filter even numbers
SELECT list_filter([1,2,3,4,5,6,7,8,9,10], x -> x % 2 = 0);
-- Result: [2, 4, 6, 8, 10]

-- Filter strings with a prefix
SELECT list_filter(['apple', 'banana', 'apricot', 'cherry'], x -> x LIKE 'app%');
-- Result: ['apple', 'apricot']

SELECT list_transform(
    list_filter([1,2,3,4,5,6,7,8,9,10], x -> x > 5),
    x -> x * 10
);
-- Result: [60, 70, 80, 90, 100]

-- Intersection of two lists
SELECT list_intersect(['laptop', 'mouse', 'keyboard'], ['laptop', 'monitor']);
-- Result: ['laptop']

-- Check if list contains a value
SELECT list_contains(['laptop', 'mouse', 'keyboard'], 'mouse');
-- Result: true

-- Check if two lists share any element
SELECT list_has_any(['laptop', 'mouse'], ['phone', 'tablet']);
-- Result: false

SELECT list_has_any(['laptop', 'mouse'], ['mouse', 'keyboard']);
-- Result: true

WITH customer_segments AS (
    SELECT 
        customer,
        array_agg(DISTINCT CASE WHEN price > 2000 THEN 'high-value' ELSE 'standard' END) AS segments
    FROM (
        SELECT o.customer, o.items, p.price
        FROM orders AS o
        CROSS JOIN UNNEST(o.items) AS items
        CROSS JOIN UNNEST(o.prices) AS prices(p, price)
    ) x
    GROUP BY customer
)
SELECT 
    a.customer AS customer_a,
    b.customer AS customer_b,
    list_intersect(a.segments, b.segments) AS shared_segments
FROM customer_segments AS a
CROSS JOIN customer_segments AS b
WHERE a.customer < b.customer;

SELECT 
    customer,
    list_slice(
        list_sort(prices, 'DESC'),
        1, 3
    ) AS top_3_prices,
    list_slice(
        list_sort(items, 'ASC'),
        1, 2
    ) AS first_2_items_alpha
FROM orders;

SELECT list_element(
    list_sort(prices),
    1
) AS lowest_price
FROM orders
WHERE customer = 'Bob';
-- Result: 8000 (only one element)

-- Concatenate two lists
SELECT list_concat([1, 2, 3], [4, 5, 6]);
-- Result: [1, 2, 3, 4, 5, 6]

-- Reverse a list
SELECT list_reverse([1, 2, 3, 4, 5]);
-- Result: [5, 4, 3, 2, 1]

-- Sort with NULL handling
SELECT list_sort([3, NULL, 1, NULL, 5], 'ASC', 'NULLS FIRST');
-- Result: [NULL, NULL, 1, 3, 5]

SELECT list_sort([3, NULL, 1, NULL, 5], 'ASC', 'NULLS LAST');
-- Result: [1, 3, 5, NULL, NULL]

SELECT 
    order_id,
    list_concat(
        list_distinct(tags_source_a),
        list_distinct(tags_source_b)
    ) AS combined_tags
FROM tag_merging_table;

import duckdb

con = duckdb.connect()

# 1. Expand nested lists
result = con.execute("""
    SELECT order_id, item
    FROM orders
    CROSS JOIN UNNEST(items) AS u(item)
""").fetchdf()

# 2. Group collect tags with dedup
result = con.execute("""
    SELECT category,
           list_sort(array_agg(tag)) AS tags,
           list_distinct(array_agg(tag)) AS unique_tags,
           list_count(array_agg(tag)) AS total_count
    FROM tags_table
    GROUP BY category
""").fetchdf()

# 3. Filter + transform
result = con.execute("""
    SELECT list_transform(
        list_filter([1,2,3,4,5,6,7,8,9,10], x -> x % 2 = 0),
        x -> x * x
    ) AS squares_of_evens
""").fetchdf()
# Result: [4, 16, 36, 64, 100]

# 4. Top-N
result = con.execute("""
    SELECT list_slice(
        list_sort(array_agg(score), 'DESC'),
        1, 5
    ) AS top_5_scores
    FROM exam_results
""").fetchdf()

import duckdb
import time

con = duckdb.connect()

# Generate 1M rows with random-sized lists
con.execute("""
    CREATE TABLE big_orders AS
    SELECT 
        generate_series AS order_id,
        'Customer_' || (generate_series % 1000)::VARCHAR AS customer,
        array_slice(
            array_generate(1, 10),
            1,
            (random() * 5 + 1)::INTEGER
        ) AS item_ids
    FROM generate_series(1, 1000000)
""")

# DuckDB LIST operations
start = time.time()
result = con.execute("""
    SELECT customer,
           list_count(array_agg(item_ids)) AS total_items
    FROM big_orders
    CROSS JOIN UNNEST(item_ids) AS u
    GROUP BY customer
""").fetchdf()
print(f"DuckDB: {time.time() - start:.3f}s")

# Equivalent Pandas operation
start = time.time()
df = con.execute("SELECT * FROM big_orders").fetchdf()
expanded = df.explode('item_ids')
result_pd = expanded.groupby('customer').size()
print(f"Pandas: {time.time() - start:.3f}s")

-- Basic Lambda
SELECT list_transform([1,2,3], x -> x * x);
-- Result: [1, 4, 9]

-- Multi-parameter Lambda (access by index)
SELECT list_transform(
    ['Alice', 'Bob', 'Carol'],
    (x, i) -> i || ': ' || upper(x)
);
-- Result: ['0: ALICE', '1: BOB', '2: CAROL']

-- Nested conditions
SELECT list_filter(
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    x -> CASE 
        WHEN x % 3 = 0 THEN true
        WHEN x % 2 = 0 THEN false
        ELSE true
    END
);
-- Result: [1, 3, 5, 6, 7, 9, 10]

-- Create nested list data
CREATE TABLE nested_data AS
SELECT * FROM (VALUES
    (1, [[1, 2], [3, 4]]),
    (2, [[5, 6]]),
    (3, [[7, 8], [9, 10], [11, 12]])
) AS t(id, matrix);

-- Flatten nested lists: one row to many
SELECT id, list_element(item, 1) AS first_col, list_element(item, 2) AS second_col
FROM nested_data
CROSS JOIN UNNEST(matrix) AS u(item);

-- List to JSON array
SELECT list_to_json(['a', 'b', 'c']);
-- Result: '["a","b","c"]'

-- JSON array to list
SELECT json_array_elements('[1,2,3]'::JSON);
-- Result: 1, 2, 3 (three rows)

-- List to JSON object (key-value pairs)
SELECT zip_with(['name', 'age', 'city'], ['Alice', 30, 'Beijing']);
-- Result: {'name': 'Alice', 'age': 30, 'city': 'Beijing'}

-- ❌ Dangerous: avg 50 items per order, 1M orders = 50M rows
SELECT * FROM orders CROSS JOIN UNNEST(products) AS p;

-- ✅ Recommended: filter before expanding
SELECT * FROM orders 
WHERE customer_id = 12345
  AND order_date >= '2026-01-01'
  CROSS JOIN UNNEST(products) AS p;

-- If items is an empty list [], CROSS JOIN UNNEST drops the row entirely
-- Use LEFT JOIN LATERAL to preserve empty-list rows
SELECT o.order_id, u.item
FROM orders AS o
LEFT JOIN LATERAL UNNEST(o.items) AS u(item) ON true;

-- ❌ Numbers and strings cannot be sorted together
SELECT list_sort([1, 'a', 2]);
-- Error: Type INTEGER and VARCHAR cannot be compared

-- ✅ Normalize types first
SELECT list_sort(list_transform([1, 'a', 2], x -> CAST(x AS VARCHAR)));