DuckDB JSON Data Processing Workshop: Nested Parsing, Array Expansion, and Production Applications

In daily data collection and analysis work, JSON is one of the most common data formats. Whether API responses, log files, or user behavior tracking events, JSON is everywhere. The traditional approach is to export JSON to Python (Pandas) for processing, but this method is slow and code-churn heavy. Today we will dive deep into DuckDB’s JSON processing capabilities — from basic parsing to advanced queries, then to performance optimization and practical monetization cases. After reading this article, you will be able to handle complex nested JSON data directly at the SQL level without exporting data to any external tools.

一、基础操作：read_json_auto 与嵌套解析

1.1 Reading Local JSON Files

DuckDB provides read_json_auto() function that can automatically detect JSON structure and infer data types:

-- Read a single JSON file
SELECT * FROM read_json_auto('users.json');

-- Read all JSON files in a directory  
SELECT * FROM read_json_auto('/data/*.json');

-- Recursively read subdirectories
SELECT * FROM read_json_auto('/data/**/*.json', recursive=true);

1.2 Parsing Nested JSON

Actual business JSON data often contains multiple levels of nesting. For example, a typical e-commerce order data:

{
    "order_id": "ORD-2024-001",
    "customer": {
        "name": "Zhang San",
        "tier": "VIP"
    },
    "items": [
        {"product": "Laptop", "price": 5999, "qty": 1},
        {"product": "Mouse", "price": 199, "qty": 2}
    ],
    "total": 6397,
    "timestamp": "2024-01-15T10:30:00Z"
}

Parse this nested structure using DuckDB:

CREATE TABLE orders AS
SELECT 
    o.order_id,
    o.customer->>'name' AS customer_name,
    o.customer->>'tier' AS customer_tier,
    o.total,
    o.timestamp
FROM read_json_auto('orders.json') o;

-- View results
SELECT * FROM orders LIMIT 5;

The ->> operator is DuckDB’s JSON extraction syntax, equivalent to json_extract_scalar(). -> returns JSON type while >> returns string type. To perform numeric calculations, cast accordingly: (json_data -> 'payload' ->> 'amount')::DECIMAL.

二、高级查询：UNNEST、聚合与反向转换

2.1 CROSS JOIN UNNEST Expand Arrays

JSON array fields are the most challenging to process. DuckDB’s CROSS JOIN UNNEST() can expand arrays into multiple rows:

-- Expand items array into one row per product record
SELECT 
    order_id,
    item->>'product' AS product_name,
    (item->>'price')::INTEGER AS price,
    (item->>'qty')::INTEGER AS quantity,
    (item->>'price')::INTEGER * (item->>'qty')::INTEGER AS line_total
FROM read_json_auto('orders.json'),
     UNNEST(items) AS item;

Output sample:

order_id   | product_name | price | quantity | line_total
-----------|-------------|-------|----------|-----------
ORD-001    | Laptop       | 5999  | 1        | 5999
ORD-001    | Mouse        | 199   | 2        | 398
ORD-002    | Keyboard     | 399   | 1        | 399

2.2 json_extract_path Chained Call

For deeply nested JSON, use json_extract_path() with multiple parameters to extract specific fields:

SELECT 
    log_timestamp,
    json_extract_path(json_data, 'user', 'id') AS user_id,
    json_extract_path(json_data, 'user', 'name') AS user_name,
    json_extract_path(json_data, 'payload', 'page') AS page,
    json_extract_path(json_data, 'payload', 'amount') AS amount
FROM api_logs;

This single query extracts all needed nested fields. Missing values automatically become NULL, which doesn’t affect subsequent processing.

2.3 json_array_elements Expand Arrays

Some scenarios contain JSON arrays, such as a user having multiple tags:

CREATE TABLE user_events AS
SELECT * FROM VALUES
    (1, '[{"type": "click", "time": "10:00"}, {"type": "scroll", "time": "10:02"}]'),
    (2, '[{"type": "purchase", "time": "11:00"}]')
AS user_events(user_id, events_json);

-- Expand array into multiple rows
SELECT 
    user_id,
    (event ->> 'type') AS event_type,
    event ->> 'time' AS event_time
FROM user_events
CROSS JOIN LATERAL json_array_elements(events_json) AS event;

Result expands each user’s multiple events into independent rows, suitable for subsequent statistics by event type.

2.4 json_group_array Reverse Aggregation

If you need to aggregate multiple rows back into a JSON array, use json_group_array():

-- Aggregate all orders per user into JSON array
SELECT 
    customer_name,
    json_group_array(ORDER_OBJECT) AS order_history
FROM (
    SELECT 
        customer_name,
        json_build_object(
            'order_id', order_id,
            'total', total,
            'date', timestamp
        ) AS ORDER_OBJECT
    FROM orders
) GROUP BY customer_name;

三、实战场景：各页面停留时长分析

Suppose we want to analyze total visit duration and page view counts from api_logs, where payload.duration might be missing:

SELECT 
    page_path AS page,
    COUNT(*) AS visit_count,
    SUM(COALESCE(payload_duration::INT, 0)) AS total_duration_seconds
FROM (
    SELECT 
        log_timestamp,
        json_data -> 'payload' ->> 'page' AS page_path,
        json_data -> 'payload' ->> 'duration' AS payload_duration
    FROM api_logs
) sub
WHERE page_path IS NOT NULL
GROUP BY page_path
ORDER BY visit_count DESC;

Results clearly show which pages are hottest and where users spend the most time.

四、进阶技巧：用 json_set 修改 JSON 数据

DuckDB supports write operations on JSON! When JSON data is incorrect, update it directly:

UPDATE api_logs
SET json_data = json_set(json_data, 'payload', 
    json_set(json_data -> 'payload', 'duration', '60'))
WHERE json_data ->> 'action' = 'click'
  AND json_extract_path(json_data, 'payload', 'duration') IS NULL;

Default fill missing click event durations with 60 seconds (in actual business, let SQL handle these dirty tasks instead of Python loops).

五、性能对比：DuckDB vs Pandas

When handling large JSON datasets, DuckDB’s performance advantage is significant. Comparison test results with 5 million user behavior JSON records:

Why is DuckDB faster?

1. Zero-copy parsing: DuckDB parses JSON directly in memory, avoiding Pandas’ multi-layer object creation
2. Parallel execution: Leverages multi-core CPU to process different shards in parallel
3. Vectorized computation: Columnar storage enables filtering and aggregation to scan only necessary columns
4. Streaming processing: For ultra-large files, DuckDB can stream-read without loading everything into memory

Actual test code:

# Pandas approach
import pandas as pd
import json

with open('events.json', 'r') as f:
    data = json.load(f)
df = pd.json_normalize(data)

# DuckDB approach
import duckdb
con = duckdb.connect()
df = con.execute("SELECT * FROM read_json_auto('events.json')").fetchdf()

六、实用小贴士

1. Check data types: When unsure if something is JSON, first use typeof(json_data) to confirm before proceeding.
2. NULL is normal: Nested fields may not exist; use COALESCE to provide defaults and avoid errors.
3. Performance note: JSON field parsing is slower than plain text. If frequently filtering by a JSON field, consider materialized views:

CREATE MATERIALIZED VIEW mv_api_logs AS
SELECT 
    log_timestamp,
    json_data ->> 'action' AS action,
    json_data -> 'payload' ->> 'page' AS page,
    json_data -> 'payload' ->> 'duration' AS duration
FROM api_logs;

-- Then create index on MV for faster queries
CREATE INDEX idx_mv_page ON mv_api_logs(page);

4. Batch import JSON files: For batches of JSON files, use read_csv specifying format=‘json’:

SELECT * FROM read_csv('logs/*.json', format='json');

5. Export data formats: After processing, export to Parquet/CSV/JSON等多种格式:

-- Export to Parquet (better compression, faster subsequent queries)
COPY (SELECT * FROM orders) TO '/data/orders.parquet' (FORMAT PARQUET);

-- Export to CSV
COPY (SELECT * FROM orders) TO '/data/orders.csv' (FORMAT CSV);

-- Export to JSON
COPY (SELECT * FROM orders) TO '/data/orders.json' (FORMAT JSON);

七、变现应用：JSON 数据处理能赚多少钱？

7.1 API Data Packaging Service

Package third-party API JSON responses processed through DuckDB into structured data services:

from fastapi import FastAPI
import duckdb

app = FastAPI()

@app.get("/api/analytics")
def get_analytics():
    con = duckdb.connect(":memory:")
    result = con.execute("""
        SELECT 
            json_extract_scalar(event, '$.type') AS event_type,
            COUNT(*) as count
        FROM read_json_auto('https://api.example.com/events.json')
        GROUP BY event_type
    """).fetchdf()
    return result.to_dict()

Monthly fee model: ¥3,000-8,000/month targeting SMEs needing API data standardization.

7.2 Log Analysis SaaS

Provide log analysis services for SMEs; DuckDB can directly parse JSON fields from Nginx/app logs:

• Supports real-time JSON log querying
• Automatically extracts key metrics (error counts, response time distribution)
• Visualization dashboard backend

Revenue model: Basic version ¥500/month, Professional version ¥2,000/month, Enterprise custom pricing.

7.3 Data Cleaning Microservice

Provide data cleaning and standardization APIs processing various dirty JSON data:

• Phone/email/address standardization
• Nested structure flattening
• Type normalization
• Missing value filling

Charge per call, ¥0.01-0.1 per call; 100K daily active users yields ¥30,000+/month revenue.

7.4 Personal Portfolio Project

Build a complete JSON data processing project as technical portfolio:

1. Scrape public APIs (GitHub API, Weather API etc.)
2. Process nested responses with DuckDB
3. Generate visual reports
4. Publish to GitHub and personal website

Recruitment managers seeing such projects often offer higher salary premiums. This is the highest ROI investment for personal branding.

7.5 Automated Report Pipeline

Build an automated JSON data acquisition-cleaning-analysis pipeline:

# daily_report.py
import duckdb
import requests
from datetime import datetime

con = duckdb.connect()

# 1. Get today's data
resp = requests.get("https://api.example.com/today/data")
data = resp.json()

# 2. Write to temp table
con.execute("CREATE TEMP TABLE raw_data (payload JSONB)")
con.execute("INSERT INTO raw_data VALUES (?)", [data])

# 3. Analyze
result = con.execute("""
    SELECT 
        country,
        COUNT(*) as visits,
        AVG(price) as avg_order_value
    FROM raw_data, UNNEST(payload.users) AS u
    GROUP BY country
""").fetchdf()

# 4. Save results
con.execute("COPY result TO '/reports/daily_{}.csv'".format(datetime.now().strftime('%Y%m%d')))

Set up Cron job to run daily generating industry report deliverables — sell as data product to relevant practitioners.

八、核心 JSON 函数速查表

九、总结

DuckDB’s JSON capabilities go far beyond just read_json_auto(). By combining json_extract_scalar(), CROSS JOIN UNNEST(), and aggregation functions, you can complete complex nested data processing tasks entirely within SQL.

Key takeaways:

1. Use read_json_auto() to auto-detect JSON structures
2. Extract nested fields using ->> or json_extract_scalar()
3. CROSS JOIN UNNEST() is the core weapon for handling JSON arrays
4. json_group_array() and json_build_object() enable reverse aggregation
5. Performance is 15-20x faster than Pandas, especially for large datasets
6. Supports read/write operations, not just querying but updating JSON data too
7. Export to multiple formats for downstream system consumption

Next time you face a mountain of nested JSON, don’t panic — fire up DuckDB and solve it in one command. Learn more DuckDB practical experience → duckdblab.org