用 DuckDB 高效分析 JSON 数据：从入门到实战
解析 JSON 文件进行分析常常充满挑战。无论你是在处理 API 响应、日志文件，还是应用数据，如果没有合适的工具，分析 JSON 都会非常耗时。
借助 DuckDB，你可以直接用 SQL 查询复杂的 JSON 文件，无需编写复杂的解析代码或搭建重量级数据库环境，就能高效分析 JSON 数据。
本文将带你了解如何使用 DuckDB 高效地查询和分析 JSON 数据，内容涵盖：
-
在你的系统上安装和配置 DuckDB
-
加载并查询 JSON 数据
-
处理嵌套的 JSON 结构
-
操作 JSON 数组和复杂对象
我们将基于真实的电商数据进行演示，介绍可立即应用于实际项目的技巧。让我们开始吧！
🔗 [GitHub 上的代码链接]
安装与启动 DuckDB
DuckDB 轻量且易于安装。下面是安装与运行 DuckDB 的步骤：
如果你使用的是 Linux 发行版，并希望通过命令行使用 duckdb，请执行以下操作：
安装 DuckDB:
$ curl https://install.duckdb.org | sh
添加到 PATH:
$ export PATH='/home/user/.duckdb/cli/latest':$PATH
通过命令行启动 DuckDB:
$ duckdb
你将看到如下界面：
v1.2.2 7c039464e4
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D
现在你已经准备就绪！
🔗 你可以查看 DuckDB 的安装文档，了解其他平台的安装指南。
使用示例 JSON 数据
我们将创建一个实用的电商数据集进行演示。该 JSON 结构包含订单、客户信息和产品明细，类似于真实电商 API 返回的数据。
📁 示例 JSON 数据存放于 ecommerce_data.json 文件中。
加载与查询 JSON 数据
现在，让我们把 JSON 数据加载到 DuckDB，并执行一些基础查询。
加载 JSON 数据
连接到 DuckDB 后，运行以下命令：
-- 从 JSON 文件创建表
CREATE TABLE ecommerce AS
SELECT * FROM read_json_auto('ecommerce_data.json');
此命令会读取 JSON 文件并自动推断数据表结构。read_json_auto 函数还能识别嵌套结构和数组。
确认数据是否正确加载：
-- 查看数据
SELECT * FROM ecommerce;
你应该会看到整个 JSON 数据以结构化表格的形式展现：
┌──────────┬───┬──────────────────────┬──────────────────────┐
│ order_id │ … │ items │ payment │
│ varchar │ │ struct(product_id … │ struct("method" va… │
├──────────┼───┼──────────────────────┼──────────────────────┤
│ ORD-1001 │ … │ [{'product_id': PR… │ {'method': credit_… │
│ ORD-1002 │ … │ [{'product_id': PR… │ {'method': paypal,… │
├──────────┴───┴──────────────────────┴──────────────────────┤
│ 2 rows 5 columns (3 shown) │
└────────────────────────────────────────────────────────────┘
基础查询示例
先来几个简单查询：
-- 统计订单数量
SELECT COUNT(*) AS order_count FROM ecommerce;
输出：
┌─────────────┐
│ order_count │
│ int64 │
├─────────────┤
│ 2 │
└─────────────┘
提取嵌套字段时，->>'name' 用于从 customer 对象中提取 name 字段文本，->> 返回文本，-> 返回 JSON。
-- 获取订单号及客户姓名
SELECT order_id,customer->>'name' AS customer_name
FROM ecommerce;
输出：
┌──────────┬───────────────┐
│ order_id │ customer_name │
│ varchar │ varchar │
├──────────┼───────────────┤
│ ORD-1001 │ Alex Johnson │
│ ORD-1002 │ Sarah Miller │
└──────────┴───────────────┘
操作嵌套 JSON 结构
JSON 的一个难点在于处理嵌套对象。比如提取客户地址信息：
-- 提取客户地址信息
SELECT order_id,customer->>'name' AS customer_name,customer->'address'->>'city' AS city,customer->'address'->>'state' AS state
FROM ecommerce;
输出：
┌──────────┬───────────────┬─────────┬─────────┐
│ order_id │ customer_name │ city │ state │
│ varchar │ varchar │ varchar │ varchar │
├──────────┼───────────────┼─────────┼─────────┤
│ ORD-1001 │ Alex Johnson │ Boston │ MA │
│ ORD-1002 │ Sarah Miller │ Seattle │ WA │
└──────────┴───────────────┴─────────┴─────────┘
你可以通过链式箭头操作符深入 JSON 结构。
还可以基于嵌套字段进行筛选：
-- 查找来自西雅图的订单
SELECT order_id,customer->>'name' AS customer_name
FROM ecommerce
WHERE customer->'address'->>'city' = 'Seattle';
输出：
┌──────────┬───────────────┐
│ order_id │ customer_name │
│ varchar │ varchar │
├──────────┼───────────────┤
│ ORD-1002 │ Sarah Miller │
└──────────┴───────────────┘
提取支付信息：
-- 获取支付详情
SELECT order_id,payment->>'method' AS payment_method,CAST(payment->>'total' AS DECIMAL) AS total_amount
FROM ecommerce;
输出：
┌──────────┬────────────────┬───────────────┐
│ order_id │ payment_method │ total_amount │
│ varchar │ varchar │ decimal(18,3) │
├──────────┼────────────────┼───────────────┤
│ ORD-1001 │ credit_card │ 179.970 │
│ ORD-1002 │ paypal │ 137.960 │
└──────────┴────────────────┴───────────────┘
注意，这里通过 CAST 将 total 转为 decimal，便于数值运算。
处理数组与复杂对象
JSON 数组需要特殊处理。来看如何展开每笔订单中的 items：
-- 将 items 数组展开为多行
SELECT order_id,customer->>'name' AS customer_name,unnest(items) AS item
FROM ecommerce;
输出：
┌──────────┬───────────────┬───────────────────────────────────────────────────┐
│ order_id │ customer_name │ item │
│ varchar │ varchar │ struct(product_id varchar, "name" varchar, cate… │
├──────────┼───────────────┼───────────────────────────────────────────────────┤
│ ORD-1001 │ Alex Johnson │ {'product_id': PROD-501, 'name': Wireless Headp… │
│ ORD-1001 │ Alex Johnson │ {'product_id': PROD-245, 'name': Smartphone Cas… │
│ ORD-1002 │ Sarah Miller │ {'product_id': PROD-103, 'name': Coffee Maker, … │
│ ORD-1002 │ Sarah Miller │ {'product_id': PROD-107, 'name': Coffee Beans P… │
└──────────┴───────────────┴───────────────────────────────────────────────────┘
unnest() 函数将 JSON 数组的每个元素转换为一行，便于 SQL 分析。
进一步提取每个 item 的字段：
-- 提取每个商品明细
SELECT order_id,customer->>'name' AS customer_name,item->>'name' AS product_name,item->>'category' AS category,CAST(item->>'price' AS DECIMAL) AS price,CAST(item->>'quantity' AS INTEGER) AS quantity
FROM (SELECT order_id,customer,unnest(items) AS itemFROM ecommerce
) AS unnested_items;
输出：
┌──────────┬───────────────┬───┬───────────────┬──────────┐
│ order_id │ customer_name │ … │ price │ quantity │
│ varchar │ varchar │ │ decimal(18,3) │ int32 │
├──────────┼───────────────┼───┼───────────────┼──────────┤
│ ORD-1001 │ Alex Johnson │ … │ 129.990 │ 1 │
│ ORD-1001 │ Alex Johnson │ … │ 24.990 │ 2 │
│ ORD-1002 │ Sarah Miller │ … │ 89.990 │ 1 │
│ ORD-1002 │ Sarah Miller │ … │ 15.990 │ 3 │
├──────────┴───────────────┴───┴───────────────┴──────────┤
│ 4 rows 6 columns (4 shown) │
└─────────────────────────────────────────────────────────┘
这里通过子查询展开 items 再提取字段，是处理嵌套数组的关键。
做一些数据分析：
-- 计算每个订单的总价值与商品数量
SELECT order_id,customer->>'name' AS customer_name,CAST(payment->>'total' AS DECIMAL) AS order_total,json_array_length(items) AS item_count
FROM ecommerce;
输出：
┌──────────┬───────────────┬───────────────┬────────────┐
│ order_id │ customer_name │ order_total │ item_count │
│ varchar │ varchar │ decimal(18,3) │ uint64 │
├──────────┼───────────────┼───────────────┼────────────┤
│ ORD-1001 │ Alex Johnson │ 179.970 │ 2 │
│ ORD-1002 │ Sarah Miller │ 137.960 │ 2 │
└──────────┴───────────────┴───────────────┴────────────┘
json_array_length() 可用于获取每个订单的商品数量。
-- 按商品类别统计平均单价
SELECT item->>'category' AS category,AVG(CAST(item->>'price' AS DECIMAL)) AS avg_price
FROM (SELECT unnest(items) AS itemFROM ecommerce
) AS unnested_items
GROUP BY category
ORDER BY avg_price DESC;
输出：
┌─────────────────┬───────────┐
│ category │ avg_price │
│ varchar │ double │
├─────────────────┼───────────┤
│ Electronics │ 129.99 │
│ Kitchen │ 89.99 │
│ Accessories │ 24.99 │
│ Food & Beverage │ 15.99 │
└─────────────────┴───────────┘
该查询先展开 items，再按类别分组计算平均价格。
总结
你已掌握使用 DuckDB 分析 JSON 数据的核心技巧。这些方法能帮你轻松应对大多数 JSON 数据分析需求。DuckDB 结合了熟悉的 SQL 语法和强大的 JSON 专用函数，让你高效处理复杂数据。
下次遇到庞杂的 JSON 数据集时，希望你能跳过繁琐的解析步骤，直奔高效分析！