DuckDB for Irregular JSON: From “Crash to Results” in 5 Minutes

The Scene: That JSON File That Keeps Data Analysts Up at Night

Have you ever encountered this scenario?

It’s Monday morning. Your business team hands you a JSON file and says, “These are user behavior logs—pull out the key metrics and analyze them.” You open the file and… wow. Tens of thousands of nested JSON records, each with a different structure. Some have user_id, some don’t. Some are nested three levels deep. Some have arrays inside objects.

You try parsing it line by line in Python? You spend hours writing code, only for it to crash with a KeyError. You try Pandas? It runs out of memory (OOM). You try opening it in Excel? Your computer freezes.

This is the classic “Ragged JSON” dilemma—where each record has inconsistent fields, and traditional methods make processing extremely painful.

But with DuckDB, you can have everything done in 5 minutes.

1. Real-World Scenario: E-commerce User Behavior Logs

Let’s say you have a user_events.json file where each record looks like this:

{
  "event_id": "evt_8a7f3b",
  "user_id": 100234,
  "timestamp": "2026-08-24T14:32:10Z",
  "event_type": "purchase",
  "properties": {
    "product_id": "prod_9921",
    "product_name": "Wireless Bluetooth Earbuds",
    "price": 299.00,
    "quantity": 1,
    "coupon_used": true,
    "tags": ["electronics", "audio", "sale"]
  },
  "device": {
    "type": "mobile",
    "os": "iOS",
    "app_version": "3.2.1"
  }
}

The problem: This JSON structure is irregular—

• Some events don’t have a device field
• Some properties have a discount field, some don’t
• The tags array has variable length
• Different export batches may have entirely different fields

Traditional Python approach:

import json

results = []
with open('user_events.json') as f:
    for line in f:
        event = json.loads(line)
        try:
            results.append({
                'event_id': event['event_id'],
                'user_id': event['user_id'],
                'product_name': event['properties']['product_name'],
                'price': event['properties']['price'],
                # If any field is missing, immediate KeyError
            })
        except KeyError as e:
            print(f"Missing key: {e}")  # Then manually handle hundreds of KeyErrors

The code volume is massive, maintenance cost is extremely high, and performance is terrible—100,000 records might take 8+ seconds.

2. The DuckDB Solution: One SQL Statement to Rule Them All

2.1 Read Directly with Automatic Schema Inference

DuckDB has built-in powerful JSON reading capabilities. read_json_auto automatically infers the schema:

import duckdb

con = duckdb.connect("ecommerce.db")

# Read JSON file directly, auto-infer structure
con.execute("""
    CREATE TABLE events AS
    SELECT * FROM read_json_auto('user_events.json')
""")

# Check table structure
print(con.execute("DESCRIBE events").fetchall())

💡 Key Point: read_json_auto automatically handles nested structures, flattening inner objects into independent columns (separated by .), like properties.price, device.type. For irregular JSON, missing fields are automatically filled with NULL—no errors.

2.2 Flexibly Extract Nested Fields—No Matter How Messy

Real-world JSON often has missing or inconsistent fields. DuckDB’s JSON_EXTRACT family of functions handles this elegantly:

# Safely extract fields, return NULL instead of crashing when missing
con.execute("""
    CREATE TABLE events_flat AS
    SELECT
        event_id,
        user_id,
        timestamp,
        event_type,
        -- Extract from properties, default to 'unknown' if missing
        COALESCE(
            JSON_EXTRACT_STRING(properties, '$.product_name'),
            'unknown'
        ) AS product_name,
        COALESCE(
            JSON_EXTRACT_FLOAT(properties, '$.price'),
            0.0
        ) AS price,
        COALESCE(
            JSON_EXTRACT_BOOLEAN(properties, '$.coupon_used'),
            false
        ) AS coupon_used,
        -- Extract tags array
        COALESCE(
            JSON_EXTRACT_STRING(properties, '$.tags'),
            '[]'
        ) AS tags,
        -- Device info
        JSON_EXTRACT_STRING(device, '$.type') AS device_type,
        JSON_EXTRACT_STRING(device, '$.os') AS device_os
    FROM events
""")

💡 Key Technique: JSON_EXTRACT_* functions return NULL when a field doesn’t exist. Combined with COALESCE for default values, you never have to worry about inconsistent data structures.

2.3 Expand Array Fields—Tag Analysis Made Easy

Array fields in JSON (like tags) are a common analysis challenge. DuckDB’s UNNEST function elegantly expands them:

# Expand tags array, one row per tag
con.execute("""
    CREATE TABLE event_tags AS
    SELECT
        event_id,
        user_id,
        tag,
        event_type,
        price
    FROM events_flat,
    UNNEST(regexp_extract_all(
        JSON_EXTRACT_STRING(properties, '$.tags'),
        '"([^"]+)"'
    )) AS t(tag)
""")

# Count events and average price per tag
result = con.execute("""
    SELECT
        tag,
        COUNT(*) AS event_count,
        ROUND(AVG(price), 2) AS avg_price,
        SUM(CASE WHEN coupon_used THEN 1 ELSE 0 END) AS coupon_events
    FROM event_tags
    GROUP BY tag
    ORDER BY event_count DESC
""").fetchdf()

print(result)

Sample output:

         tag  event_count  avg_price  coupon_events
0    electronics       12453     312.50           3421
1         audio        8921     289.00           2103
2          sale        7654     198.50           4521
3       clothing        5432     156.00           1230

2.4 Handle Irregular JSON—Different Fields Across Records

This is the most headache-inducing scenario: some events’ properties have a discount field, some don’t. Some have referrer, some don’t.

DuckDB’s COALESCE + JSON_EXTRACT_* combination solves this perfectly:

# Dynamically merge differently-structured JSON attributes
con.execute("""
    CREATE TABLE events_unified AS
    SELECT
        event_id,
        user_id,
        event_type,
        timestamp,
        -- Extract all possible fields, fill NULL or default if missing
        COALESCE(JSON_EXTRACT_FLOAT(properties, '$.price'), 0) AS price,
        COALESCE(JSON_EXTRACT_FLOAT(properties, '$.discount'), 0) AS discount,
        COALESCE(JSON_EXTRACT_STRING(properties, '$.referrer'), 'direct') AS referrer,
        COALESCE(JSON_EXTRACT_STRING(properties, '$.coupon_code'), '') AS coupon_code,
        COALESCE(JSON_EXTRACT_STRING(properties, '$.shipping_method'), 'standard') AS shipping,
        device
    FROM events
""")

# Analyze conversion rate by referrer
result = con.execute("""
    SELECT
        referrer,
        COUNT(*) AS total_events,
        SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchases,
        ROUND(
            SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
        ) AS convert_rate_pct
    FROM events_unified
    GROUP BY referrer
    ORDER BY total_events DESC
""").fetchdf()

print(result)

3. Performance Comparison: DuckDB vs Python Line-by-Line Parsing

Test environment: 8-core 16GB MacBook Pro, JSON file ~2GB (irregular nested structure).

DuckDB’s performance advantages come from:

• Columnar reading: Only reads needed fields, skips unrelated columns
• Vectorized execution: Processes 2,048 rows at a time instead of one-by-one interpretation
• Zero-copy memory management: Avoids Python object creation overhead

4. Comparison with Traditional Tools

5. Best Practices: 5 Tips for JSON Processing

1. Prefer read_json_auto Over Manual Schema Definition

DuckDB auto-infer types, especially friendly for irregular JSON.

# Auto-infer, no need to manually specify column names and types
con.execute("CREATE TABLE events AS SELECT * FROM read_json_auto('data.json')")

2. Use JSON_EXTRACT_* Instead of -> Operator

JSON_EXTRACT_STRING(col, '$.field') is more intuitive than col->>'field' and type-safe.

# Recommended: explicit type, optional defaults
JSON_EXTRACT_STRING(properties, '$.product_name')

# Not recommended: implicit type inference, hard to debug errors
properties->>'product_name'

3. Array Expansion with UNNEST + regexp_extract_all

This is the most elegant way to handle JSON array tags—10x faster than Python’s json.loads loop.

# One-line array expansion
UNNEST(regexp_extract_all(JSON_EXTRACT_STRING(properties, '$.tags'), '"([^"]+)"'))

4. Use COALESCE for Irregular Fields

Avoid NULL propagation causing calculation errors, making downstream analysis more robust.

COALESCE(JSON_EXTRACT_FLOAT(properties, '$.price'), 0) AS price,
COALESCE(JSON_EXTRACT_STRING(properties, '$.referrer'), 'direct') AS referrer

5. Filter Before Parsing for Large Files

# Only parse needed columns, saving 50%+ memory
con.execute("""
    CREATE TABLE events_subset AS
    SELECT event_id, user_id, event_type,
           properties->>'$.price' AS price,
           properties->>'$.tags' AS tags
    FROM read_json_auto('huge_file.json')
    WHERE event_type IN ('purchase', 'add_to_cart')
""")

6. Monetization: How Much Can This Skill Earn You?

Scenario 1: Data Cleaning Freelance Service

Market Demand: Many SMEs have log data (in JSON format) but lack data processing capabilities. They’re willing to pay for “quick structured data turnaround.”

Pricing Strategy:

• Basic service (single JSON file parsing): $30-70 per job
• Advanced service (multi-file merge + irregular field handling): $140-420 per job
• Monthly retainer (continuous data ingestion + automated reports): $700-2,100/month

Customer Acquisition:

• Freelance platforms (Upwork, Fiverr, Chinese platforms like 猪八戒)
• WeChat/QQ groups (SME owner groups)
• Zhihu answers on “how to handle irregular JSON” for traffic

Scenario 2: Data Analysis SaaS Product

Product Positioning: JSON log analysis tool—users upload JSON files, auto-infer schema, generate visual reports.

Core Features:

• Automatic schema inference (DuckDB read_json_auto)
• Interactive SQL query interface
• One-click CSV/Excel export
• Scheduled task automation

Pricing:

• Free tier: 3 uploads/month, 10MB limit
• Pro tier: $14/month, unlimited uploads, 1GB limit
• Enterprise tier: $70/month, private deployment + API access

MVP Development Time: 2-3 weeks (DuckDB + Streamlit + FastAPI)

Scenario 3: Data Analysis Training

Course Design:

• “DuckDB JSON in Action”: 4-hour recorded course + 10 practical cases
• Pricing: $28/person (early bird $14)
• Target audience: data analysts, BI engineers, Python developers

Promotion Channels:

• CSDN technical blog traffic
• Zhihu column serialization
• Bilibili free intro videos

Scenario 4: Corporate Training

Enterprise Pain Point: Data analysts spend 80% of time on data cleaning, only 20% on truly valuable analysis.

Solution:

• Provide DuckDB JSON processing training for enterprise data teams
• Custom internal data cleaning pipelines
• Pricing: $700-2,800 per session (half-day training)

7. Summary

Irregular JSON data processing is a daily pain point for data analysts. Traditional Python solutions have high code volume, poor performance, and high maintenance costs. DuckDB, through read_json_auto, JSON_EXTRACT_* functions, and UNNEST array expansion, can handle complex JSON parsing with a single SQL line—30-40x faster than Python.

Key Takeaways:

1. read_json_auto auto-infer schema
2. JSON_EXTRACT_* + COALESCE for safe extraction
3. UNNEST + regexp_extract_all for array expansion
4. Filter large files before parsing

Next Step: Find a JSON file you have, try DuckDB’s read_json_auto, and see how much you can accomplish in 5 minutes.

Full code and more JSON processing tips → duckdblab.org