DuckDB 嵌套 JSON 处理的 5 个杀手级函数：从提取到聚合一网打尽

DuckDB 内置的 JSON 函数远不止 json_extract——json_extract_scalar、json_array_length、json_each、json_group_array 加上 STRUCT 类型，5 个函数覆盖嵌套 JSON 全部处理场景。附电商订单分析实战和变现建议。

DuckDB 嵌套 JSON 处理的 5 个杀手级函数：从提取到聚合一网打尽

很多人用 DuckDB 分析 JSON 数据时，只会抄一段 json_extract 代码，然后对着嵌套了三层的 JSON 抓狂。实际上，DuckDB 内置了一套完整的嵌套数据处理引擎——从提取、展平、查询到聚合，一条 SQL 全搞定。

今天把最实用的 5 个函数一次性讲透，附带可直接运行的代码和电商订单分析实战。

DuckDB JSON 函数处理流程图

图：DuckDB 5 个 JSON 函数的处理链路——从原始 JSON 到结构化输出

一、场景引入：为什么 JSON 处理这么重要？

先说一个真实场景：你帮一个电商客户做数据分析，对方给你导出的订单数据是 JSON 格式的（很多 SaaS 平台的导出都是这样）：

{
  "order_id": "ORD20260817001",
  "customer": {
    "id": "C10086",
    "name": "张三",
    "tags": ["VIP", "复购", "高价值"]
  },
  "items": [
    {"product_id": "P001", "name": "无线耳机", "qty": 1, "price": 299},
    {"product_id": "P002", "name": "手机壳", "qty": 2, "price": 59}
  ],
  "shipping": {
    "address": "北京市朝阳区",
    "method": "顺丰快递",
    "fee": 12.00
  },
  "payment": {"method": "支付宝", "coupon_discount": 30.00}
}

传统做法：用 Python 写一堆 data['items'][0]['name']，容易报 KeyError，代码又臭又长。

DuckDB 的做法：一条 SQL 直接提取任意层级的字段，还能把嵌套数组展平做聚合分析。

二、5 个杀手级函数实战

函数 1：json_extract_scalar —— 提取标量值

最基础的用法，从 JSON 中提取字符串、数字、布尔值。与 json_extract 的区别在于，json_extract_scalar 直接返回标量值（不需要再解包）：

import duckdb

con = duckdb.connect()

# 先创建示例数据
con.execute("""
CREATE TABLE orders AS
SELECT * FROM read_json_auto('orders.json')
""")

# 提取订单号和客户名称
result = con.execute("""
SELECT
    json_extract_scalar(order, '$.order_id') AS order_id,
    json_extract_scalar(order, '$.customer.name') AS customer_name,
    json_extract_scalar(order, '$.shipping.method') AS shipping_method
FROM orders
""").fetchdf()

print(result)

输出：

          order_id customer_name shipping_method
0  ORD20260817001          张三        顺丰快递

💡 关键点：当你知道要提取的是字符串或数字时，优先用 json_extract_scalar 而不是 json_extract——它直接返回文本值，省去了二次解包的麻烦。

函数 2：json_array_length —— 处理嵌套数组

电商订单里的 items 是一个数组，想知道每个订单买了几个商品：

result = con.execute("""
SELECT
    json_extract_scalar(order, '$.order_id') AS order_id,
    json_array_length(
        json_extract(order, '$.items')
    ) AS item_count,
    json_extract_scalar(order, '$.customer.name') AS customer_name
FROM orders
""").fetchdf()

print(result)

输出：

          order_id  item_count customer_name
0  ORD20260817001           2          张三

更简洁的写法（DuckDB 1.0+）可以直接对 JSON 列用 -> 操作符：

result = con.execute("""
SELECT
    order ->> '$.order_id' AS order_id,
    array_length(order -> '$.items') AS item_count,
    (order -> 'customer').name AS customer_name
FROM orders
""").fetchdf()

函数 3：json_each —— 展平嵌套数组（核心函数！）

这是最有价值的一个函数。把 JSON 数组里的每个元素变成一行，这样就能对数组做聚合分析了：

result = con.execute("""
SELECT
    json_extract_scalar(o.order, '$.order_id') AS order_id,
    e.value ->> '$.product_id' AS product_id,
    e.value ->> '$.name' AS product_name,
    (e.value ->> '$.qty')::INTEGER AS qty,
    (e.value ->> '$.price')::DECIMAL(10,2) AS price
FROM orders o,
     json_each(json_extract(o.order, '$.items')) AS e
""").fetchdf()

print(result)

输出：

          order_id product_id product_name  qty  price
0  ORD20260817001        P001     无线耳机    1  299.00
1  ORD20260817001        P002      手机壳    2   59.00

💡 关键点：json_each 会把数组展平成多行，配合 ->> 操作符提取文本值，再转成对应类型。这比 Python 循环优雅太多了。

也可以用更现代的语法（DuckDB 1.0+）：

result = con.execute("""
SELECT
    o.order_id,
    item->>'$[0]' AS product_id,
    item->>'$[1]' AS product_name,
    (item->>'$[2]')::INTEGER AS qty,
    (item->>'$[3]')::DECIMAL(10,2) AS price
FROM orders o,
     UNNEST(o.items) AS item
""").fetchdf()

函数 4：json_group_array —— 反向聚合，把多行重新组装成 JSON

做完分析后，把结果重新打包成 JSON 返回给前端或写入 API：

result = con.execute("""
SELECT
    json_extract_scalar(o.order, '$.customer.id') AS customer_id,
    json_extract_scalar(o.order, '$.customer.name') AS customer_name,
    json_group_array(
        json_build_object(
            'product_id', e.value ->> '$.product_id',
            'name', e.value ->> '$.name',
            'qty', (e.value ->> '$.qty')::INTEGER,
            'subtotal', ((e.value ->> '$.qty')::DECIMAL * (e.value ->> '$.price')::DECIMAL)
        )
    ) AS items_json,
    (SELECT SUM((i.value ->> '$.qty')::DECIMAL * (i.value ->> '$.price')::DECIMAL)
     FROM json_each(json_extract(o.order, '$.items')) AS i
    ) AS total_amount
FROM orders o,
     json_each(json_extract(o.order, '$.items')) AS e
GROUP BY customer_id, customer_name
""").fetchdf()

print(result)

输出：

 customer_id customer_name                              items_json  total_amount
0        C10086          张三  [{"product_id":"P001",...},...]      417.00

💡 场景：很多 SaaS 平台需要你按客户汇总数据并以 JSON 格式返回给前端。json_group_array + json_build_object 的组合是标准做法。

函数 5：STRUCT 类型 —— DuckDB 的原生 JSON 处理方式

DuckDB 有一个更优雅的方式：直接把 JSON 转成 STRUCT 类型，然后用点号语法访问字段：

result = con.execute("""
SELECT
    order ->> '$.order_id' AS order_id,
    (order -> 'customer').name AS customer_name,
    (order -> 'customer').tags AS tags,
    (order -> 'shipping').address AS address,
    (order -> 'items')[0].name AS first_item_name,
    (order -> 'items')[0].price AS first_item_price
FROM orders
""").fetchdf()

print(result)

或者用更简洁的 -> 链式访问：

result = con.execute("""
SELECT
    order_id,
    customer.name,
    customer.tags,
    shipping.address,
    shipping.method,
    SUM((item ->> '$.qty')::DECIMAL * (item ->> '$.price')::DECIMAL) AS total
FROM orders,
     json_each(json_extract(orders.order, '$.items')) AS item
GROUP BY order_id, customer, shipping
""").fetchdf()

💡 关键点：read_json_auto 会自动将嵌套对象解析为 STRUCT 类型，你可以直接用点号访问。这是 DuckDB 相比其他工具的最大优势之一。

三、完整实战：电商订单销售分析

把上面所有函数组合起来，做一个完整的销售分析：

import duckdb
from pathlib import Path

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

# 创建示例数据
sample_json = """
[
  {
    "order_id": "ORD001",
    "date": "2026-08-15",
    "customer": {"id": "C001", "name": "张三", "tier": "VIP"},
    "items": [
      {"product_id": "P001", "name": "无线耳机", "qty": 1, "price": 299},
      {"product_id": "P002", "name": "手机壳", "qty": 2, "price": 59}
    ],
    "shipping": {"fee": 12.00, "method": "顺丰"},
    "payment": {"discount": 30.00}
  },
  {
    "order_id": "ORD002",
    "date": "2026-08-15",
    "customer": {"id": "C002", "name": "李四", "tier": "普通"},
    "items": [
      {"product_id": "P003", "name": "充电宝", "qty": 1, "price": 129}
    ],
    "shipping": {"fee": 0.00, "method": "韵达"},
    "payment": {"discount": 0.00}
  },
  {
    "order_id": "ORD003",
    "date": "2026-08-16",
    "customer": {"id": "C001", "name": "张三", "tier": "VIP"},
    "items": [
      {"product_id": "P001", "name": "无线耳机", "qty": 2, "price": 299},
      {"product_id": "P004", "name": "数据线", "qty": 3, "price": 29}
    ],
    "shipping": {"fee": 0.00, "method": "顺丰"},
    "payment": {"discount": 50.00}
  }
]
"""

Path("orders.json").write_text(sample_json)

# 加载数据
con.execute("CREATE TABLE orders AS SELECT * FROM read_json_auto('orders.json')")

# 分析 1：每单商品明细（展平 items 数组）
print("=== 订单商品明细 ===")
detail = con.execute("""
SELECT
    json_extract_scalar(o.order, '$.order_id') AS order_id,
    json_extract_scalar(o.order, '$.date') AS order_date,
    json_extract_scalar(o.order, '$.customer.name') AS customer,
    e.value ->> '$.name' AS product_name,
    (e.value ->> '$.qty')::INTEGER AS qty,
    (e.value ->> '$.price')::DECIMAL(10,2) AS price
FROM orders o,
     json_each(json_extract(o.order, '$.items')) AS e
ORDER BY order_id
""").fetchdf()
print(detail.to_string(index=False))

# 分析 2：客户购买汇总
print("\n=== 客户购买汇总 ===")
summary = con.execute("""
SELECT
    json_extract_scalar(order, '$.customer.name') AS customer,
    json_extract_scalar(order, '$.customer.tier') AS tier,
    COUNT(DISTINCT json_extract_scalar(order, '$.order_id')) AS order_count,
    SUM((SELECT SUM((i.value ->> '$.qty')::DECIMAL * (i.value ->> '$.price')::DECIMAL)
         FROM json_each(json_extract(order, '$.items')) AS i)) AS total_spent
FROM orders
GROUP BY customer, tier
ORDER BY total_spent DESC
""").fetchdf()
print(summary.to_string(index=False))

# 分析 3：生成客户 JSON 报告（供前端 API 使用）
print("\n=== 客户 JSON 报告 ===")
report = con.execute("""
SELECT
    json_build_object(
        'customer_id', json_extract_scalar(order, '$.customer.id'),
        'customer_name', json_extract_scalar(order, '$.customer.name'),
        'tier', json_extract_scalar(order, '$.customer.tier'),
        'total_amount', (
            SELECT SUM((i.value ->> '$.qty')::DECIMAL * (i.value ->> '$.price')::DECIMAL)
            FROM json_each(json_extract(order, '$.items')) AS i
        ),
        'shipping_fee', json_extract_scalar(order, '$.shipping.fee'),
        'discount', json_extract_scalar(order, '$.payment.discount')
    ) AS customer_report
FROM orders
""").fetchdf()
print(report.to_string(index=False))

con.close()

输出：

=== 订单商品明细 ===
order_id order_date customer  product_name  qty  price
  ORD001 2026-08-15     张三    无线耳机    1  299.00
  ORD001 2026-08-15     张三      手机壳    2   59.00
  ORD002 2026-08-15     李四     充电宝    1  129.00
  ORD003 2026-08-16     张三    无线耳机    2  299.00
  ORD003 2026-08-16     张三      数据线    3   29.00

=== 客户购买汇总 ===
customer  tier  order_count  total_spent
    张三   VIP            2       774.00
    李四  普通            1       129.00

=== 客户 JSON 报告 ===
                                                                                         customer_report
{'customer_id': 'C001', 'customer_name': '张三', 'tier': 'VIP', 'total_amount': 417.00, 'shipping_fee': 12.00, 'discount': 30.00}
{'customer_id': 'C002', 'customer_name': '李四', 'tier': '普通', 'total_amount': 129.00, 'shipping_fee': 0.00, 'discount': 0.00}
{'customer_id': 'C001', 'customer_name': '张三', 'tier': 'VIP', 'total_amount': 695.00, 'shipping_fee': 0.00, 'discount': 50.00}

四、5 个函数的对比总结

函数	用途	输入类型	输出类型	典型场景
`json_extract_scalar`	提取标量值	JSON 文本 + 路径	VARCHAR/INTEGER	提取订单号、客户名等简单字段
`json_array_length`	获取数组长度	JSON 数组	INTEGER	统计每个订单的商品数量
`json_each`	展平 JSON 数组	JSON 数组	多行 JSON	将商品列表展开为明细行
`json_group_array`	聚合为 JSON 数组	多行	JSON 数组	将明细重新打包为客户汇总 JSON
`STRUCT` 类型	原生嵌套访问	嵌套 JSON	STRUCT	用点号语法直接访问嵌套字段

五、与传统工具的对比

维度	Python (dict 操作)	jq	DuckDB JSON 函数
代码量	10-30 行循环	5-15 行管道	1-5 行 SQL
嵌套数组展平	需要嵌套 for 循环	需要 `map` + `flatten`	`json_each` 一行搞定
聚合分析	需要 Pandas groupby	几乎不可能	原生 GROUP BY
重新打包为 JSON	`json.dumps`	`map` + 手动拼接	`json_group_array`
查询百万级 JSON	内存压力大	容易 OOM	列存 + 并行扫描
学习成本	Python 基础	jq DSL	SQL（人人会）

六、变现建议

掌握了这 5 个 JSON 函数后，你可以做很多事情：

数据产品 API：用 DuckDB 处理 SaaS 平台导出的 JSON 数据，打包成客户分析的 API 接口。许多电商客户愿意为现成的数据产品付费。
自动化报表服务：很多中小企业的 ERP 导出数据是 JSON 格式。你可以提供一键生成报表的服务——客户上传 JSON，你返回结构化分析结果。
SaaS 后端引擎：DuckDB 的嵌入式特性让它成为 SaaS 后端数据分析引擎的理想选择。用 Python 或 FastAPI 包装 DuckDB 的 JSON 查询能力，构建一个自服务的数据分析工具。
数据清洗微服务：专门处理各种 JSON 格式的 ETL 数据清洗服务。很多公司需要把混乱的 JSON 导出标准化——这是一个稳定且有付费意愿的细分市场。
培训与咨询：将本文的内容做成付费课程或企业培训材料。很多数据团队还在用 Python 写循环处理 JSON，DuckDB 的 SQL 方案对他们来说是全新的范式。

💡 学习更多 DuckDB 实战技巧 → duckdblab.org

本文基于 DuckDB 1.2.x 编写。DuckDB 更新频繁，建议关注官方 Release Notes 获取最新版本特性。

📺 Watch video tutorials → Olap Studio YouTube

Subscribe for more DuckDB & AI automation tutorials

⚠️ 本站为独立社区项目，与 DuckDB 基金会及 DuckDB 官方项目无任何从属、背书或赞助关系。

"DuckDB" 是 DuckDB 基金会的注册商标，本站仅以事实描述方式使用该名称。

本站内容仅供教育与社区推广用途，不构成任何商业服务。