Figure: DuckDB native JSON parsing architecture—from API response to analysis results, zero intermediate layer
Introduction: How Many Times Has JSON Parsing Tripped You Up?
Have you ever encountered this scenario:
- Called a third-party API and got complex nested JSON data back
- Tried parsing with Python? Wrote a bunch of
.get()calls andforloops—code is long and fragile - When data volume grows, pandas eats all your memory
- Worst case: the JSON structure changes, fields appear and disappear, code needs constant updates
The traditional approach is to parse JSON into Python objects, stuff them into a DataFrame, then write SQL. Three steps, each one prone to failure.
Today I’ll show you how to use DuckDB’s native JSON support to query nested JSON directly as a table—no parsing code, no memory explosions, and automatic adaptation when structures change.
The Core Problem This Solves
You don’t need to convert JSON to structured data before analyzing it—DuckDB can operate on JSON directly within SQL.
Typical scenarios:
- Call Weibo/Douyin/Twitter APIs to get post data, directly analyze nested
user,stats,textfields - Parse e-commerce product detail JSON (specifications, prices, inventory are often nested)
- Process server logs (JSON-formatted access logs with inconsistent fields)
- Read complex structures from GitHub API, Jira API, Slack API
Step 1: Start with Real Nested JSON
Suppose you called a mock “product review API” that returns data like this:
[
{
"review_id": "R001",
"product": {"sku": "A100", "name": "Wireless Mouse", "category": "Peripherals"},
"user": {"level": "gold", "location": "Beijing"},
"rating": 5,
"comment": "Great feel, stable connection",
"tags": ["Comfortable", "Durable"],
"created_at": "2026-09-20T10:30:00Z"
},
{
"review_id": "R002",
"product": {"sku": "A100", "name": "Wireless Mouse", "category": "Peripherals"},
"user": {"level": "silver", "location": "Shanghai"},
"rating": 4,
"comment": "Good value for money, but the left button feels soft",
"tags": ["Value"],
"created_at": "2026-09-19T15:22:00Z"
},
{
"review_id": "R003",
"product": {"sku": "B200", "name": "Mechanical Keyboard", "category": "Peripherals"},
"user": {"level": "bronze", "location": "Guangzhou"},
"rating": 3,
"comment": "Too loud, not suitable for office use",
"tags": ["Noisy"],
"created_at": "2026-09-18T09:15:00Z"
}
]
Key point: This isn’t a flat CSV. product and user are nested objects, and tags is an array. Traditional SQL cannot handle this structure.
Step 2: DuckDB Reads JSON Directly—Zero Parsing Code
import duckdb
import json
# Simulated JSON data from API
api_response = '[{"review_id": "R001", "product": {"sku": "A100", "name": "Wireless Mouse", "category": "Peripherals"}, "user": {"level": "gold", "location": "Beijing"}, "rating": 5, "comment": "Great feel, stable connection", "tags": ["Comfortable", "Durable"], "created_at": "2026-09-20T10:30:00Z"}]'
# One line of code: turn JSON into a queryable table
con = duckdb.connect("reviews.db")
con.execute(f"CREATE TABLE reviews AS SELECT * FROM read_json_auto([{api_response}])")
# Verify: check the table structure directly
print(con.execute("DESCRIBE reviews").fetchall())
# Output shows product and user are STRUCT types, tags is VARCHAR[] array
read_json_auto automatically infers nested structures: objects become STRUCT, arrays become VARCHAR[]. No need to hand-write schemas.
Step 3: Access Nested Fields Directly in SQL—Dot Notation
# ── Query 1: Average rating per SKU (extract from nested product) ──
avg_rating = con.execute("""
SELECT
product->>'$.sku' AS sku,
product->>'$.name' AS name,
ROUND(AVG(rating), 1) AS avg_rating,
COUNT(*) AS review_count
FROM reviews
GROUP BY sku, name
ORDER BY avg_rating DESC
""").fetchdf()
print(avg_rating)
# Output:
# sku name avg_rating review_count
# 0 A100 Wireless Mouse 4.5 2
# 1 B200 Mechanical Keyboard 3.0 1
# ── Query 2: Review distribution from premium users (gold tier) ──
gold_reviews = con.execute("""
SELECT
user->>'$.level' AS user_level,
user->>'$.location' AS location,
AVG(rating) AS avg_rating
FROM reviews
GROUP BY user_level, location
ORDER BY avg_rating DESC
""").fetchdf()
print(gold_reviews)
DuckDB provides two ways to access nested fields:
column->>'$.path'— returns text (suitable for extracting single fields)column->>'$.path'::INTEGER— with type conversion- You can also use
column.fielddot notation (DuckDB native support)
Step 4: Handle Array Fields—UNNEST to Expand tags
# ── Query 3: Count mentions per tag (tags is an array, needs expansion) ──
tag_stats = con.execute("""
SELECT
tag,
COUNT(*) AS mention_count,
AVG(rating) AS avg_rating_when_mentioned
FROM reviews,
UNNEST(tags) AS tag -- expand array into multiple rows
GROUP BY tag
ORDER BY mention_count DESC
""").fetchdf()
print(tag_stats)
# Output:
# tag mention_count avg_rating_when_mentioned
# 0 Value 1 4.0
# 1 Comfortable 1 5.0
# 2 Durable 1 5.0
# 3 Noisy 1 3.0
# ── Query 4: Find reviews containing "Value" keyword ──
value_reviews = con.execute("""
SELECT review_id, comment, rating
FROM reviews
WHERE 'Value' = ANY(tags)
OR comment ILIKE '%Value%'
""").fetchdf()
print(value_reviews)
UNNEST(tags) AS tag is DuckDB’s core technique for handling arrays—splits one row into multiple rows, one per array element. Syntax is identical to PostgreSQL.
Step 5: Persist JSON as Tables—For Future Analysis
# ── Approach A: Flatten all parsed JSON fields into a table ──
con.execute("""
CREATE TABLE reviews_flat AS
SELECT
review_id,
product->>'$.sku' AS product_sku,
product->>'$.name' AS product_name,
product->>'$.category' AS category,
user->>'$.level' AS user_level,
user->>'$.location' AS location,
rating,
comment,
tags,
CAST(created_at AS TIMESTAMP) AS created_at
FROM reviews
""")
# ── Approach B: Store raw JSON only, parse on demand (saves space) ──
con.execute("""
CREATE TABLE reviews_raw AS
SELECT review_id, CAST(json_dump AS VARCHAR) AS raw_json, created_at
FROM reviews
""")
# Key difference:
# Approach A: Fast queries, larger storage (fields stored redundantly)
# Approach B: Saves space, but parses JSON on every query
# Recommendation: Use Approach A for < 1M rows, Approach B for > 1M rows
Step 6: Full API Response Handling—Complete实战Flow
Suppose you want to scrape product reviews from an e-commerce platform (simulating API calls):
import duckdb
import json
from datetime import datetime
def fetch_and_analyze_reviews(api_url, limit=1000):
"""
Fetch review data from API and analyze directly
Note: Using simulated data instead of real API calls
"""
# Step 1: Get JSON data (in real projects, replace with requests.get)
# response = requests.get(api_url, headers={"Authorization": "Bearer YOUR_TOKEN"})
# json_data = response.json()
# Simulate batch API response data
json_data = [
{
"review_id": f"R{i:03d}",
"product": {"sku": f"S{i%5+1:03d}", "name": f"Product{i}", "category": "Digital"},
"user": {"level": ["gold", "silver", "bronze"][i%3], "location": ["Beijing","Shanghai","Guangzhou","Shenzhen"][i%4]},
"rating": (i % 5) + 1,
"comment": "This is a simulated review comment",
"tags": ["好用", "推荐"][i%2:],
"created_at": f"2026-09-{(i%28)+1:02d}T10:00:00Z"
}
for i in range(1, limit + 1)
]
# Step 2: Analyze directly with DuckDB, no Python parsing needed
con = duckdb.connect(":memory:") # In-memory database, destroyed after use
con.execute(f"CREATE TABLE reviews AS SELECT * FROM read_json_auto({json.dumps(json_data)})")
# Step 3: Multi-dimensional analysis—one SQL query does it all
insights = {}
# Average rating per SKU
insights['sku_ratings'] = con.execute("""
SELECT product->>'$.sku' AS sku, product->>'$.name' AS name,
ROUND(AVG(rating), 1) AS avg_rating, COUNT(*) AS cnt
FROM reviews GROUP BY sku, name ORDER BY avg_rating DESC
""").fetchdf().to_dict('records')
# User sentiment by location
insights['location_sentiment'] = con.execute("""
SELECT user->>'$.location' AS location,
AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews GROUP BY location ORDER BY avg_rating DESC
""").fetchdf().to_dict('records')
# Tag statistics
insights['tag_stats'] = con.execute("""
SELECT tag, COUNT(*) AS cnt, AVG(rating) AS avg_rating
FROM reviews, UNNEST(tags) AS tag
GROUP BY tag ORDER BY cnt DESC LIMIT 10
""").fetchdf().to_dict('records')
# Negative review alerts (rating <= 2)
insights['negative_reviews'] = con.execute("""
SELECT review_id, product->>'$.name' AS product,
rating, comment, created_at
FROM reviews WHERE rating <= 2
ORDER BY created_at DESC LIMIT 5
""").fetchdf().to_dict('records')
return insights
# Run analysis
results = fetch_and_analyze_reviews("https://api.example.com/reviews", limit=500)
print(f"✅ Analysis complete, found {len(results['negative_reviews'])} negative reviews")
The entire process involves zero lines of JSON parsing code. read_json_auto handles nested structures automatically, UNNEST handles arrays, and SQL completes all aggregation analysis directly.
Step 7: Handle “Unstable Structure” JSON—Fault-Tolerant Queries
Real-world API responses often have inconsistent JSON structures—some records have tags, others don’t; sometimes product is a string instead of an object. DuckDB has friendly fault tolerance mechanisms:
# ── Safe access: returns NULL instead of error when field is missing ──
safe_query = con.execute("""
SELECT
review_id,
product->>'$.sku' AS sku,
COALESCE(product->>'$.sku', 'UNKNOWN') AS sku_safe,
CASE WHEN tags IS NOT NULL AND array_length(tags) > 0
THEN tags[1] ELSE 'No tags' END AS first_tag
FROM reviews
""").fetchdf()
# ── Filter out records with JSON parsing failures ──
# DuckDB's read_json_auto automatically skips malformed rows
valid_count = con.execute("""
SELECT COUNT(*) FROM reviews
WHERE is_json(review_id || ' - this is a valid review')
""").fetchone()[0]
COALESCE(field, 'default_value') is the golden rule for handling missing nested JSON fields—never assume a field always exists.
Performance Comparison: DuckDB vs Python Parsing
| Dimension | Python Parsing Approach | DuckDB Direct Query |
|---|---|---|
| Code Volume | 30-50 lines of parsing logic | 1-3 lines of SQL |
| Memory Usage | Loads everything into memory first | On-demand reading, supports streaming |
| Structure Changes | Code must be updated | read_json_auto adapts automatically |
| Query Performance | Slow pandas merges | DuckDB vectorized execution, 10x+ faster |
| Reusability | Rewrite parsing each time | SQL script written once, reused forever |
| Error Tolerance | KeyError crashes immediately | COALESCE + fault-tolerant parsing |
Core advantage: Combines “parse JSON” and “analyze data” into one step, eliminating the intermediate Python layer.
Tonight’s Action Items
- Find a JSON dataset you have on hand (API response, log file, config file—anything)
- Use
read_json_autoto load it directly and see what structure DuckDB infers - Write a SQL query using
->>dot notation to access nested fields - If your data has array fields, try
UNNEST - Compare results with Python parsing—code volume and speed differences will be striking
Remember: When you encounter nested JSON, don’t rush to write Python parsing code. Ask DuckDB first if it can query it directly. In most cases, it can.
For more DuckDB实战 tips, follow DuckDB Lab (duckdblab.org)