一、问题场景：嵌套 JSON 与列表数据怎么处理？
在真实的数据分析工作中，我们经常会碰到两类"长得不太规整"的数据：
- 嵌套 JSON 数据：API 返回的 JSON 层层嵌套，有的字段在深层对象里，有的是数组；
- 列表/数组字段：电商订单的商品列表、用户的标签数组、时间序列的多点数据。
传统做法是什么？用 Python 的 json.loads() 一层层 dict.get()，或者写循环遍历数组——代码冗长、难以调试、性能差。
DuckDB 提供了两个强大的武器来解决这些问题：
json_tree()：一键将任意嵌套 JSON 展平为关系表list_transform/list_reduce/list_filter：在 SQL 中对列表做逐元素变换和聚合
下面我们通过 6 个实战场景，看看它们如何让你的数据处理效率翻倍。
二、场景一：json_tree() 一键展平嵌套 JSON
json_tree() 是 DuckDB 处理嵌套 JSON 的杀手锏。它能把任意深度的 JSON 树展开成一个包含 key、value、type、fullkey（JSONPath）、path 等列的关系表。
2.1 基本用法
SELECT * FROM json_tree('[
{"user": "Alice", "orders": [
{"id": 1, "amount": 100, "items": ["book", "pen"]},
{"id": 2, "amount": 250}
]},
{"user": "Bob", "orders": [
{"id": 3, "amount": 50}
]}
]');
返回的每一行代表 JSON 树中的一个节点，fullkey 列给出了该节点的完整 JSONPath，方便你精确定位。
2.2 从文件中读取 NDJSON 并展平
-- 读取 NDJSON 文件并展平
SELECT fullkey, value::VARCHAR AS name
FROM json_tree(read_ndjson_auto('users.json'))
WHERE key = 'name';
配合 read_ndjson_auto()，你可以直接处理 JSON Lines 文件，无需先将数据加载到 Python 中。
2.3 提取特定字段
-- 只提取每个订单的 amount
SELECT
fullkey,
value::BIGINT AS amount
FROM json_tree('[
{"user": "Alice", "orders": [
{"id": 1, "amount": 100},
{"id": 2, "amount": 250}
]}
]')
WHERE key = 'amount';
2.4 从 API 响应中提取数据
这是最常见的实战场景——从第三方 API 返回的嵌套 JSON 中提取你需要的字段：
-- 模拟 API 返回的嵌套响应
WITH api_response AS (
SELECT '{
"status": "success",
"data": {
"store": "Shop_A",
"products": [
{"sku": "A001", "price": 29.99, "stock": 150},
{"sku": "A002", "price": 49.99, "stock": 0},
{"sku": "A003", "price": 19.99, "stock": 80}
],
"meta": {"total_items": 3, "currency": "USD"}
}
}' AS json_data
)
SELECT
key,
value,
type,
fullkey
FROM api_response,
json_tree(json_data::JSON)
WHERE fullkey LIKE '%.sku%' OR fullkey LIKE '%.price%';
结果：
| key | value | type | fullkey |
|---|---|---|---|
| sku | A001 | STRING | $.data.products[0].sku |
| price | 29.99 | DOUBLE | $.data.products[0].price |
| sku | A002 | STRING | $.data.products[1].sku |
| price | 49.99 | DOUBLE | $.data.products[1].price |
| sku | A003 | STRING | $.data.products[2].sku |
| price | 19.99 | DOUBLE | $.data.products[2].price |
三、场景二：list_transform — 列表逐元素变换
如果你用过 Pandas 的 apply，那 list_transform 就是你的新宠——在 SQL 里直接对列表的每个元素做变换。
3.1 电商折扣计算
WITH orders AS (
SELECT 1 AS order_id, list_value(100.0, 250.0, 80.0) AS amounts
UNION ALL SELECT 2, list_value(50.0, 120.0)
UNION ALL SELECT 3, list_value(200.0, 300.0, 150.0, 90.0)
)
SELECT
order_id,
list_transform(amounts, (x) -> x * 0.8) AS discounted_amounts,
list_transform(amounts, (x) -> round(x * 0.8, 2)) AS rounded_discounted
FROM orders;
结果：
| order_id | discounted_amounts | rounded_discounted |
|---|---|---|
| 1 | [80.0, 200.0, 64.0] | [80.0, 200.0, 64.0] |
| 2 | [40.0, 96.0] | [40.0, 96.0] |
| 3 | [160.0, 240.0, 120.0, 72.0] | [160.0, 240.0, 120.0, 72.0] |
3.2 字符串列表处理
-- 将所有标签转为大写
SELECT
user_id,
list_transform(tags, (t) -> upper(t)) AS uppercase_tags
FROM (
SELECT 1 AS user_id, list_value('vip_gold', 'regular_user', 'new_member') AS tags
);
-- 结果: ['VIP_GOLD', 'REGULAR_USER', 'NEW_MEMBER']
3.3 条件变换：只修改符合条件的元素
-- 将库存为 0 的商品标记为 "out_of_stock"
SELECT
product_id,
list_transform(stock_levels, (s) -> CASE WHEN s = 0 THEN 'out_of_stock' ELSE s END) AS status_flags
FROM (
SELECT 1 AS product_id, list_value(150, 0, 80, 0, 25) AS stock_levels
);
四、场景三：list_reduce — 列表聚合
list_reduce 类似 SQL 中的 SUM，但它在列表级别工作——对列表中的每个元素做累积计算。
4.1 计算列表总和
SELECT
order_id,
list_reduce(amounts, (acc, x) -> acc + x) AS total
FROM (
SELECT 1 AS order_id, list_value(100, 250, 80) AS amounts
UNION ALL SELECT 2, list_value(50, 120)
UNION ALL SELECT 3, list_value(200, 300, 150, 90)
);
4.2 计算列表平均值
SELECT
user_id,
list_reduce(scores, (acc, x) -> acc + x) / list_length(scores)::DOUBLE AS avg_score
FROM (
SELECT 1 AS user_id, list_value(85, 92, 78, 95) AS scores
UNION ALL SELECT 2, list_value(60, 75, 80)
);
4.3 更复杂的 reduce：累积求和
-- 计算累积和
SELECT
day_id,
list_reduce(daily_sales, (acc, x) -> acc || (list_last(acc) + x)) AS cumulative_sales
FROM (
SELECT 1 AS day_id, list_value(100, 150, 200, 180, 250) AS daily_sales
);
-- 结果: [100, 250, 450, 630, 880]
五、场景四：list_filter — 条件筛选列表元素
-- 从用户标签中筛选 VIP 标签
SELECT
user_id,
list_filter(tags, t -> t LIKE 'VIP%') AS vip_tags
FROM (
SELECT 1 AS user_id, list_value('VIP_gold', 'regular', 'VIP_platinum', 'trial') AS tags
UNION ALL SELECT 2, list_value('regular', 'trial')
UNION ALL SELECT 3, list_value('VIP_silver', 'premium', 'VIP_diamond')
);
结果：
| user_id | vip_tags |
|---|---|
| 1 | [‘VIP_gold’, ‘VIP_platinum’] |
| 2 | [] |
| 3 | [‘VIP_silver’, ‘VIP_diamond’] |
5.1 数字列表的条件筛选
-- 筛选出销售额超过 100 的记录
SELECT
region,
list_filter(sales, s -> s > 100) AS high_sales
FROM (
SELECT 'North' AS region, list_value(80, 150, 200, 50) AS sales
UNION ALL SELECT 'South', list_value(120, 90, 180, 60)
);
六、场景五：组合拳 — json_tree + list_transform + list_reduce
真正的威力在于将这些函数组合使用。下面是一个完整的电商订单分析示例：
6.1 从 NDJSON 读取订单数据并分析
-- 假设有一个 orders.ndjson 文件，每行是一个订单：
-- {"order_id": 1, "items": [{"name": "Book", "price": 29.99, "qty": 2}, ...]}
-- {"order_id": 2, "items": [{"name": "Pen", "price": 5.99, "qty": 5}, ...]}
-- 第一步：展平订单结构
WITH flattened AS (
SELECT
o.order_id,
item.value::VARCHAR AS item_name,
(item.value::JSON)->>'price' AS price,
(item.value::JSON)->>'qty' AS qty
FROM read_ndjson_auto('orders.ndjson') o,
LATERAL UNNEST(o.items) AS t(item)
),
-- 第二步：按订单汇总
order_totals AS (
SELECT
order_id,
item_name,
price::DOUBLE * qty::DOUBLE AS line_total
FROM flattened
)
SELECT
order_id,
list_transform(list_value(100, 200, 150), (x) -> round(x * 0.9, 2)) AS discounted,
list_reduce(list_value(100, 200, 150), (acc, x) -> acc + x) AS total
FROM order_totals;
6.2 从 API 获取实时数据并分析
-- 模拟从电商 API 获取的实时库存数据
WITH api_data AS (
SELECT '[
{"warehouse": "WH_A", "products": [
{"sku": "A001", "stock": 150, "min_stock": 50},
{"sku": "A002", "stock": 30, "min_stock": 50},
{"sku": "A003", "stock": 200, "min_stock": 50}
]},
{"warehouse": "WH_B", "products": [
{"sku": "B001", "stock": 5, "min_stock": 20},
{"sku": "B002", "stock": 100, "min_stock": 20}
]}
]' AS json_data
)
SELECT
warehouse,
sku,
stock::INT AS stock_level,
min_stock::INT AS threshold,
stock::INT - min_stock::INT AS buffer,
CASE WHEN stock::INT < min_stock::INT THEN 'REORDER' ELSE 'OK' END AS alert_status
FROM api_data,
json_tree(json_data::JSON)
WHERE fullkey LIKE '%.warehouse'
OR fullkey LIKE '%.sku'
OR fullkey LIKE '%.stock'
OR fullkey LIKE '%.min_stock';
七、与传统工具对比
| 维度 | Python (json + loop) | jq | DuckDB json_tree + list_* |
|---|---|---|---|
| 安装依赖 | Python 环境 | ~2MB | ~50MB 单文件 |
| 嵌套 JSON 展平 | 递归函数，易出错 | 需多层管道 | 一行 json_tree() |
| 列表逐元素变换 | map() / 列表推导式 | 不支持 | list_transform() |
| 列表聚合 | sum() / reduce() | 不支持 | list_reduce() |
| 列表条件筛选 | filter() | 不支持 | list_filter() |
| 并行处理 | 需 multiprocessing | 单线程 | 自动并行 |
| SQL 集成 | 需 pandas/sqlalchemy | 无 | 原生 SQL |
| 大数据量 | 受限于内存 | 受限于内存 | 列式存储，流式处理 |
结论：对于小到中等规模的 JSON 数据处理（<10GB），DuckDB 的
json_tree()+ 列表函数组合提供了最佳的性价比——零依赖、单文件、SQL 驱动、自动并行。
八、性能提示
8.1 使用 read_ndjson_auto 而非 read_json_auto
对于 JSON Lines 格式的文件（每行一个 JSON 对象），read_ndjson_auto() 比 read_json_auto() 更快，因为它不需要将整个文件加载到内存中：
-- 快！流式读取 NDJSON
SELECT * FROM read_ndjson_auto('large_orders.ndjson');
-- 慢！需要预扫描整个文件来推断 schema
SELECT * FROM read_json_auto('large_orders.json');
8.2 配合 WHERE 过滤减少数据量
在展平之前先过滤，可以减少 json_tree() 的处理量：
-- 先过滤再展平（更高效）
SELECT fullkey, value
FROM read_ndjson_auto('orders.ndjson')
WHERE (data->>'status') = 'completed'
AND (data->>'total')::DOUBLE > 100
LIMIT 1000;
8.3 利用 EXPLAIN ANALYZE 优化查询
EXPLAIN ANALYZE
SELECT
order_id,
list_reduce(items, (acc, x) -> acc + (x->>'price')::DOUBLE * (x->>'qty')::DOUBLE) AS total
FROM read_ndjson_auto('orders.ndjson');
九、变现建议
掌握这些 DuckDB 高级 JSON 和列表处理能力，可以帮你打开多种变现渠道：
构建数据产品：用
json_tree()+list_transform()搭建 API 数据清洗管道，为跨境电商卖家提供竞品价格监控 SaaS。每月收费 $29-$99，边际成本几乎为零。自动化报表服务：为中小企业搭建基于 DuckDB 的自动化报表系统。每周从多个 API 拉取数据，用
list_reduce做聚合，自动生成 PDF 报告。按项目收费 ¥5000-¥20000。数据清洗即服务：很多公司收到客户传来的各种格式的 JSON 数据，需要清洗后入库。用 DuckDB 的
json_tree()可以快速处理这些脏数据，提供按次计费的数据清洗服务。技术培训课程：制作"DuckDB 高级数据处理"系列课程，覆盖 json_tree、list_transform、list_reduce 等主题。在 Udemy、慕课网或知识星球上线，定价 ¥99-¥299。
咨询与外包：很多团队在处理嵌套 JSON 时还在用 Python 写复杂的递归解析器。你能用一行
json_tree()替代，这就是你的差异化优势。为企业提供 DuckDB 迁移咨询服务。
DuckDB json_tree() 与列表函数的数据处理流程
本文使用的是 DuckDB 1.5.x 版本。DuckDB 持续快速迭代中，建议定期关注官方 Release Notes。