DuckDB’s Nested Data Trinity: LIST, STRUCT, MAP in One SQL Query

Ever encountered this scenario: an API returns JSON data where each order contains multiple products, and each product has an array of tags. You need to count “how many times each tag appears.” In pandas, you’d write loop after loop — verbose code, poor performance, and bugs lurking around every corner.

The root cause isn’t bad code — it’s using row-oriented thinking for nested data.

DuckDB natively supports three nested data types: LIST (arrays), STRUCT (objects), and MAP (key-value pairs). A single SQL query can accomplish what previously required dozens of lines of Python code.

Figure: The processing pipeline for DuckDB’s three nested data types

1. LIST Type: The Foundation of Nested Data

LIST is the cornerstone of DuckDB’s nested data handling. Its syntax is elegant and powerful.

1.1 Basic Syntax

-- Create a table with LIST columns
CREATE TABLE orders (
    order_id INTEGER,
    tags LIST(VARCHAR),
    amounts LIST(DOUBLE)
);

-- Insert data
INSERT INTO orders VALUES
    (1, ['Electronics', 'Hot Seller', 'Phone'], [7999.00, 99.00]),
    (2, ['Clothing', 'Summer'], [199.00]),
    (3, ['Electronics', 'Computer', 'Premium'], [12999.00, 299.00, 59.00]);

1.2 UNNEST: The Core Operation

UNNEST is the most important operation — it expands arrays into rows:

-- Expand each tag into its own row
SELECT 
    order_id,
    tag
FROM orders,
     UNNEST(tags) AS tag;

Output:

┌──────────┬─────────────┐
│ order_id │    tag      │
├──────────┼─────────────┤
│    1     │ Electronics │
│    1     │ Hot Seller  │
│    1     │    Phone    │
│    2     │  Clothing   │
│    2     │   Summer    │
│    3     │ Electronics │
│    3     │  Computer   │
│    3     │   Premium   │
└──────────┴─────────────┘

1.3 Real-World: Tag Statistics

-- Count how many times each tag appears
SELECT 
    tag,
    COUNT(*) AS usage_count,
    COUNT(DISTINCT order_id) AS order_count
FROM orders,
     UNNEST(tags) AS tag
GROUP BY tag
ORDER BY usage_count DESC;

Output:

┌─────────────┬───────────────┬───────────────┐
│    tag      │ usage_count   │ order_count   │
├─────────────┼───────────────┼───────────────┤
│ Electronics │      2        │      2        │
│   Summer    │      2        │      2        │
│ Hot Seller  │      1        │      1        │
│    Phone    │      1        │      1        │
│  Clothing   │      1        │      1        │
│  Computer   │      1        │      1        │
│   Premium   │      1        │      1        │
└─────────────┴───────────────┴───────────────┘

1.4 LIST Functions Reference

2. STRUCT Type: Object-Like Nested Fields

STRUCT lets you access nested fields like object properties — clean and intuitive.

2.1 Basic Syntax

-- Create a table with STRUCT columns
CREATE TABLE employees (
    id INTEGER,
    info STRUCT(
        name VARCHAR,
        age INTEGER,
        skills LIST(VARCHAR),
        address STRUCT(
            city VARCHAR,
            district VARCHAR
        )
    )
);

-- Insert data
INSERT INTO employees VALUES
    (1, STRUCT('Alice', 30, ['Python', 'SQL'], STRUCT('Beijing', 'Chaoyang'))),
    (2, STRUCT('Bob', 25, ['Java', 'Go'], STRUCT('Shanghai', 'Pudong'))),
    (3, STRUCT('Charlie', 35, ['Python', 'R'], STRUCT('Guangzhou', 'Tianhe')));

2.2 Dot-Notation Access

-- Access nested fields with dot notation
SELECT 
    info.name AS name,
    info.age AS age,
    info.skills[1] AS primary_skill,
    info.address.city AS city
FROM employees;

Output:

┌─────────┬──────┬────────────┬───────────┐
│  name   │ age  │primary_skill│   city    │
├─────────┼──────┼────────────┼───────────┤
│  Alice  │  30  │   Python   │  Beijing  │
│   Bob   │  25  │    Java    │ Shanghai  │
│ Charlie │  35  │   Python   │ Guangzhou │
└─────────┴──────┴────────────┴───────────┘

2.3 STRUCT Filtering and Aggregation

-- Filter: employees older than 28
SELECT info.name, info.skills
FROM employees
WHERE info.age > 28;

-- Aggregate: count how many people have each skill
SELECT 
    UNNEST(info.skills) AS skill,
    COUNT(*) AS count
FROM employees
GROUP BY skill
ORDER BY count DESC;

3. MAP Type: Dynamic Key-Value Pairs

MAP handles dynamic fields and irregular structures — something LIST and STRUCT can’t do as elegantly.

3.1 Basic Syntax

-- Create a table with MAP columns
CREATE TABLE user_configs (
    user_id INTEGER,
    settings MAP(VARCHAR, VARCHAR),
    preferences MAP(VARCHAR, INTEGER)
);

-- Insert data
INSERT INTO user_configs VALUES
    (1, MAP({'theme': 'dark', 'lang': 'zh', 'notifications': 'true'}),
         MAP({'clicks': 1500, 'views': 8000, 'orders': 23})),
    (2, MAP({'theme': 'light', 'lang': 'en', 'notifications': 'false'}),
         MAP({'clicks': 300, 'views': 1200, 'orders': 5})),
    (3, MAP({'theme': 'dark', 'lang': 'ja', 'notifications': 'true'}),
         MAP({'clicks': 2000, 'views': 10000, 'orders': 45}));

3.2 MAP Queries

-- Query specific keys
SELECT 
    user_id,
    settings['theme'] AS theme,
    settings['lang'] AS language,
    preferences['clicks'] AS click_count
FROM user_configs;

-- Filter: users with dark theme
SELECT * 
FROM user_configs 
WHERE settings['theme'] = 'dark';

3.3 MAP Functions Reference

4. Combined Real-World: Extracting Nested Data from JSON APIs

In practice, most nested data comes from JSON APIs. DuckDB can read and flatten them directly.

4.1 Read JSON and Expand Nested Arrays

-- Assume orders.json format:
-- [{"order_id":1, "items":[{"name":"iPhone","price":7999,"tags":["Electronics","Hot Seller"]}]}, ...]

SELECT 
    order_id,
    item.name AS product_name,
    item.price AS price,
    item.tags[1] AS primary_tag
FROM read_json_auto('orders.json'),
     LATERAL UNNEST(items) AS item;

4.2 Multi-Level Nested Expansion

-- Extract all tags from orders and count usage
SELECT 
    tag,
    COUNT(*) AS usage_count
FROM read_json_auto('orders.json'),
     LATERAL UNNEST(items) AS item,
     LATERAL UNNEST(item.tags) WITH ORDINALITY AS tags(tag, i)
GROUP BY tag
ORDER BY usage_count DESC;

4.3 STRUCT + LIST Combined Usage

-- Create complex orders: each order has multiple products (STRUCT in LIST)
CREATE TABLE orders_complex AS
SELECT * FROM (
    VALUES
    (1, [
        STRUCT('iPhone', 7999.00, ['Electronics', 'Hot Seller']),
        STRUCT('Case', 99.00, ['Accessories'])
    ]),
    (2, [
        STRUCT('T-Shirt', 199.00, ['Clothing', 'Summer']),
        STRUCT('Shorts', 149.00, ['Clothing', 'Summer'])
    ]),
    (3, [
        STRUCT('MacBook', 12999.00, ['Electronics', 'Computer', 'Premium'])
    ])
) AS t(order_id, items);

-- Calculate total per order
SELECT 
    order_id,
    SUM(item.col1) AS total
FROM orders_complex,
     UNNEST(items) AS item
GROUP BY order_id;

5. Three Types Comparison

6. Comparison with Traditional Approaches

7. Monetization Strategies

With DuckDB nested data processing skills, here are several monetization paths:

7.1 Data Cleaning SaaS Service

Provide API data cleaning services for e-commerce and SaaS companies. Many platform APIs return deeply nested JSON that can be flattened with a single DuckDB query — replacing what previously took hours of Python development.

• Target: Cross-border e-commerce, DTC brands
• Pricing: Per-project ¥5,000-20,000, monthly maintenance ¥2,000-5,000

7.2 DuckDB Training Courses

Offer online/offline training for data teams on advanced LIST/STRUCT/MAP techniques.

• Course pricing: ¥299-999/person
• Corporate training: ¥5,000-20,000/day

7.3 Build Data Products with DuckDB Backend

Use DuckDB nested types to rapidly build analytics product backends:

• User behavior tag analysis system (LIST for tags)
• Multi-tenant configuration management (MAP for dynamic configs)
• Product catalog management (STRUCT for category hierarchies)

7.4 Open Source Tool + Paid Plugins

Build open-source tools around DuckDB nested data (e.g., duckdb-nested-tools), monetize through paid plugins or enterprise editions.

• Open source core: UNNEST visualizer, STRUCT field extractor
• Paid features: Multi-level nesting preview, performance optimization tools

7.5 Content Monetization

Publish DuckDB nested data tutorials on Medium, Dev.to, Zhihu, and other platforms. Technical content has strong long-tail SEO value and can generate revenue through ads, affiliate marketing, and paid subscriptions.

Summary

DuckDB’s LIST, STRUCT, and MAP nested data types let you handle nested data processing with a single SQL query that previously required dozens of lines of Python.

Core mental model:

• LIST → use UNNEST for array expansion
• STRUCT → use . dot notation for nested field access
• MAP → use ['key'] subscript for dynamic field queries
• Combined → three levels of nesting (LIST of STRUCT with MAP) handled in one line

Next time you face the pain of “one field storing multiple values,” skip the loops — with DuckDB’s nested types, one SQL query does it all.

This article is based on DuckDB 1.2.x. DuckDB evolves rapidly — check the official Release Notes for the latest features.