DuckDB Direct JSON Parsing——Say Goodbye to Python Manual Parsing, One SQL Query Handles API Data Extraction

Practical Skills

DuckDB Direct JSON Parsing——Say Goodbye to Python Manual Parsing, One SQL Query Handles API Data Extraction

Use DuckDB's read_json_auto and UNNEST to query nested JSON directly without Python parsing code. Compare traditional approaches in code volume, memory usage, and performance, with a complete e-commerce review API analysis实战.

DuckDB Direct JSON Parsing Architecture

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 and for loops—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, text fields
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.field dot 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.

Monetization Advice: How to Make Money With This Skill

1. Data Service Side Hustle (Beginner Level)

Many small companies need to analyze API data but can’t afford a full-time data engineer. You can offer:

Pricing: 500-2000 RMB per project, based on data volume and complexity
Customers: E-commerce sellers, social media operators, small startups
Deliverables: A Python + DuckDB script + analysis report

2. Automated Data Monitoring SaaS (Advanced Level)

Build a lightweight SaaS where users connect their own APIs, get automatic analysis and daily reports:

Pricing: 99-299 RMB/month
Target Users: Small teams needing real-time sales/user data monitoring
Tech Stack: DuckDB + FastAPI + scheduled tasks

3. Standardized Data Products (Expert Level)

Template common API analysis scenarios:

Social media sentiment analysis reports
E-commerce review sentiment analysis
Log anomaly detection

Each product priced at 999-4999 RMB with near-zero marginal cost.

4. Content Monetization

Write DuckDB + JSON实战 series articles on Zhihu,掘金, WeChat Official Accounts, driving traffic to duckdblab.org for full tutorials and template code.

Tonight’s Action Items

Find a JSON dataset you have on hand (API response, log file, config file—anything)
Use read_json_auto to 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)

📺 Watch video tutorials → Olap Studio YouTube

Subscribe for more DuckDB & AI automation tutorials