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')
""")

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

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。