DuckDB 直接解析嵌套 JSON——告别 Python 手写解析，一条 SQL 搞定 API 数据提取

DuckDB 直接解析嵌套 JSON——告别 Python 手写解析，一条 SQL 搞定 API 数据提取

用 DuckDB 的 read_json_auto 和 UNNEST 直接查询嵌套 JSON，无需 Python 解析代码。对比传统方案的代码量、内存占用和性能差距，附完整电商评论 API 分析实战。

DuckDB 直接解析嵌套 JSON 架构图

图：DuckDB 原生 JSON 解析架构——从 API 响应到分析结果，零中间层

引言：你被 JSON 解析坑过多少次？

有没有遇到过这种场景：

调用了一个第三方 API，返回了复杂嵌套的 JSON 数据
用 Python 解析？写了一堆 .get() 和 for 循环，代码又长又容易崩
数据量一大，pandas 读进来直接内存爆掉
最惨的是：JSON 结构还会变，字段时有时没有，代码天天改

传统做法是把 JSON 先解析成 Python 对象，再塞进 DataFrame，再写 SQL。三步走，每一步都可能出问题。

今天教你用 DuckDB 的 JSON 原生支持，直接把嵌套 JSON 当表查——不写解析代码，不爆内存，结构变了也能自动处理。

这个方案解决的核心问题

你不需要先把 JSON 转换成结构化数据再分析——DuckDB 可以直接在 SQL 里操作 JSON。

典型场景：

调用微博/抖音/Twitter API 获取帖子数据，直接分析嵌套的 user、stats、text 字段
解析电商平台的商品详情 JSON（规格、价格、库存往往是嵌套结构）
处理服务器日志（JSON 格式的访问日志，字段不固定）
读取 GitHub API、Jira API、Slack API 返回的复杂结构

第一步：从一份真实嵌套 JSON 开始

假设你调用了一个模拟的「商品评价 API」，返回数据如下：

[
  {
    "review_id": "R001",
    "product": {"sku": "A100", "name": "无线鼠标", "category": "外设"},
    "user": {"level": "gold", "location": "北京"},
    "rating": 5,
    "comment": "手感很好，连接稳定",
    "tags": ["舒适", "耐用"],
    "created_at": "2026-09-20T10:30:00Z"
  },
  {
    "review_id": "R002",
    "product": {"sku": "A100", "name": "无线鼠标", "category": "外设"},
    "user": {"level": "silver", "location": "上海"},
    "rating": 4,
    "comment": "性价比不错，但左键有点软",
    "tags": ["性价比"],
    "created_at": "2026-09-19T15:22:00Z"
  },
  {
    "review_id": "R003",
    "product": {"sku": "B200", "name": "机械键盘", "category": "外设"},
    "user": {"level": "bronze", "location": "广州"},
    "rating": 3,
    "comment": "声音太大，办公室不适合",
    "tags": ["噪音大"],
    "created_at": "2026-09-18T09:15:00Z"
  }
]

关键：这不是一个扁平的 CSV，product 和 user 都是嵌套对象，tags 是数组。传统 SQL 完全无法处理这种结构。

第二步：DuckDB 直接读 JSON——零解析代码

import duckdb
import json

# 模拟从 API 拿到的 JSON 数据
api_response = '[{"review_id": "R001", "product": {"sku": "A100", "name": "无线鼠标", "category": "外设"}, "user": {"level": "gold", "location": "北京"}, "rating": 5, "comment": "手感很好，连接稳定", "tags": ["舒适", "耐用"], "created_at": "2026-09-20T10:30:00Z"}]'

# 一行代码：把 JSON 变成可查询的表
con = duckdb.connect("reviews.db")
con.execute(f"CREATE TABLE reviews AS SELECT * FROM read_json_auto([{api_response}])")

# 验证：直接看表结构
print(con.execute("DESCRIBE reviews").fetchall())
# 输出显示 product 和 user 是 STRUCT 类型，tags 是 VARCHAR[] 数组

read_json_auto 会自动推断嵌套结构：对象变成 STRUCT，数组变成 VARCHAR[]。你不需要手写 schema。

第三步：SQL 直接访问嵌套字段——点号语法

# ── 查询 1：每个 SKU 的平均评分（从嵌套的 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)
# 输出：
#    sku   name  avg_rating  review_count
# 0  A100  无线鼠标         4.5             2
# 1  B200  机械键盘         3.0             1

# ── 查询 2：高级用户（gold）的评价分布 ──
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 提供两种访问嵌套字段的方式：

column->>'$.path' —— 返回文本（适合提取单个字段）
column->>'$.path'::INTEGER —— 带类型转换
也可以用 column.field 的点号语法（DuckDB 原生支持）

第四步：处理数组字段——UNNEST 展开 tags

# ── 查询 3：每个标签被提及的次数（tags 是数组，需要展开）──
tag_stats = con.execute("""
    SELECT
        tag,
        COUNT(*) AS mention_count,
        AVG(rating) AS avg_rating_when_mentioned
    FROM reviews,
         UNNEST(tags) AS tag   -- 把数组展开成多行
    GROUP BY tag
    ORDER BY mention_count DESC
""").fetchdf()
print(tag_stats)
# 输出：
#       tag  mention_count  avg_rating_when_mentioned
# 0   性价比              1                      4.0
# 1     舒适              1                      5.0
# 2     耐用              1                      5.0
# 3    噪音大              1                      3.0

# ── 查询 4：找出评论里包含「性价比」关键词的评价 ──
value_reviews = con.execute("""
    SELECT review_id, comment, rating
    FROM reviews
    WHERE '性价比' = ANY(tags)
    OR comment ILIKE '%性价比%'
""").fetchdf()
print(value_reviews)

UNNEST(tags) AS tag 是 DuckDB 处理数组的核心技巧——把一行拆成多行，每行一个数组元素。和 PostgreSQL 的语法完全一致。

第五步：把 JSON 直接存成表——方便后续分析

# ── 方式 A：直接把 JSON 解析后的字段全部展开成扁平表 ──
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
""")

# ── 方式 B：只存原始 JSON，需要时再解析（省空间）──
con.execute("""
    CREATE TABLE reviews_raw AS
    SELECT review_id, CAST(json_dump AS VARCHAR) AS raw_json, created_at
    FROM reviews
""")

# 两种策略的区别：
# 方式 A：查询快，但存储空间大（字段重复存储）
# 方式 B：存储省，但每次查询都要解析 JSON
# 建议：数据量 < 100万行用方式 A，> 100万行用方式 B

第六步：处理 API 响应——完整实战流程

假设你要爬取某个电商平台的商品评论（模拟 API 调用）：

import duckdb
import json
from datetime import datetime

def fetch_and_analyze_reviews(api_url, limit=1000):
    """
    从 API 获取评论数据并直接分析
    注意：这里用模拟数据代替真实 API 调用
    """
    # Step 1: 获取 JSON 数据（实际项目中替换为 requests.get）
    # response = requests.get(api_url, headers={"Authorization": "Bearer YOUR_TOKEN"})
    # json_data = response.json()

    # 模拟 API 返回的批量数据
    json_data = [
        {
            "review_id": f"R{i:03d}",
            "product": {"sku": f"S{i%5+1:03d}", "name": f"商品{i}", "category": "数码"},
            "user": {"level": ["gold", "silver", "bronze"][i%3], "location": ["北京","上海","广州","深圳"][i%4]},
            "rating": (i % 5) + 1,
            "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: 直接用 DuckDB 分析，无需 Python 解析
    con = duckdb.connect(":memory:")  # 内存数据库，用完即销毁

    con.execute(f"CREATE TABLE reviews AS SELECT * FROM read_json_auto({json.dumps(json_data)})")

    # Step 3: 多维度分析——一条 SQL 搞定
    insights = {}

    # 各 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')

    # 各城市用户评价分布
    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')

    # 标签统计
    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')

    # 差评预警（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

# 运行分析
results = fetch_and_analyze_reviews("https://api.example.com/reviews", limit=500)
print(f"✅ 分析完成，发现 {len(results['negative_reviews'])} 条差评记录")

整个过程没有写一行 JSON 解析代码。read_json_auto 自动处理嵌套结构，UNNEST 处理数组，SQL 直接完成所有聚合分析。

第七步：处理「结构不稳定」的 JSON——容错查询

真实世界的 API 返回的 JSON 结构往往不统一——有的记录有 tags，有的没有；有的 product 是字符串而不是对象。DuckDB 对此有友好的容错机制：

# ── 安全访问：字段不存在时返回 NULL 而不是报错 ──
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 '无标签' END AS first_tag
    FROM reviews
""").fetchdf()

# ── 过滤掉 JSON 解析失败的记录 ──
# DuckDB 的 read_json_auto 会自动跳过格式错误的行
valid_count = con.execute("""
    SELECT COUNT(*) FROM reviews
    WHERE is_json(review_id || ' - 这是一条有效的review')
""").fetchone()[0]

COALESCE(field, '默认值') 是处理嵌套 JSON 字段缺失的黄金法则——永远不要假设字段一定存在。

性能对比：DuckDB vs Python 解析

维度	Python 解析方案	DuckDB 直接查询
代码量	30-50 行解析逻辑	1-3 行 SQL
内存占用	先全量加载到内存	按需读取，支持流式
嵌套结构变化	代码要跟着改	read_json_auto 自动适应
查询性能	pandas merge 慢	DuckDB 向量化执行，快 10x+
可复用性	每次都要重写解析	SQL 脚本一次写好反复用
错误容忍	KeyError 直接崩	COALESCE + 容错解析

核心优势：把「解析 JSON」和「分析数据」两件事合二为一，省掉了中间的 Python 层。

变现建议：如何用这个技能赚钱

1. 数据服务副业（入门级）

很多中小公司需要分析 API 数据但养不起数据工程师。你可以提供：

定价：500-2000 元/次，按数据量和复杂度收费
客户：电商卖家、自媒体运营、小型创业公司
交付物：一份 Python + DuckDB 脚本 + 分析结果报告

2. 自动化数据监控 SaaS（进阶级）

做一个轻量级 SaaS，让用户连接自己的 API，自动分析并推送日报：

定价：99-299 元/月
目标用户：需要实时监控销售/用户数据的中小团队
技术栈：DuckDB + FastAPI + 定时任务

3. 数据产品标准化（专家级）

把常见的 API 分析场景模板化：

社交媒体舆情分析报告
电商评论情感分析
日志异常检测

每个产品定价 999-4999 元，边际成本趋近于零。

4. 内容变现

在知乎、掘金、微信公众号写 DuckDB + JSON 实战系列文章，引流到 duckdblab.org 获取完整版教程和模板代码。

今晚行动

找一个你手头的 JSON 数据（API 响应、日志文件、配置文件都行）
用 read_json_auto 直接读进去，看看 DuckDB 自动推断出的结构
写一条 SQL，用 ->> 点号语法访问嵌套字段
如果数据里有数组字段，试试 UNNEST
把结果和用 Python 解析的方案对比——代码量和运行速度差距会很大

记住：遇到嵌套 JSON 别急着写 Python 解析，先问 DuckDB 能不能直接查。大多数情况下，它都能。

更多 DuckDB 实战技巧，请关注 DuckDB Lab（duckdblab.org）

📺 Watch video tutorials → Olap Studio YouTube

Subscribe for more DuckDB & AI automation tutorials