DuckDB + SQL 高效分析 JSON 数据

你有没有过这样的经历？从某个 API 抓下来一堆 JSON，或者从 App 里导出了自己的数据，想分析一下，结果发现 JSON 嵌套得像个迷宫。

用 Python 写脚本？可以，但写起来麻烦，调试也费劲。

用 Excel？它连嵌套的 JSON 都打不开。

这时候，DuckDB + SQL 就像一把瑞士军刀，让你用最熟悉的 SQL，直接查询 JSON 文件，不用解析，不用建表，开箱即用。

DuckDB 是一个嵌入式的分析型数据库，轻量、单文件、无需服务器。

它最大的亮点之一就是能直接读取 JSON，并且自动推断结构。

下面，我们用一个电商订单数据的例子，看看如何用 DuckDB + SQL 轻松完成从简单统计到复杂嵌套数组的分析。

这些技巧，你完全可以迁移到自己的日志文件、API 响应、甚至个人数据导出上。

安装 DuckDB

安装 DuckDB 非常简单。在 Linux 或 macOS 终端里执行：

curl https://install.duckdb.org | sh
export PATH='/home/user/.duckdb/cli/latest':$PATH
duckdb

最后一行会启动 DuckDB 的 SQL 交互界面。

如果你更喜欢持久化数据库，可以用 .open mydb.duckdb 打开一个文件。

整个过程就像安装一个普通的命令行工具，没有复杂的配置，没有依赖地狱。

让 DuckDB 读懂你的 JSON

假设你从电商平台导出了订单数据 ecommerce_data.json。

每个订单大概长这样：有 order_id，有 customer（里面嵌套了 name 和 address），有 payment（包含 method 和 total），

还有 items 数组（每个元素有 name、category、price、quantity）。

在 DuckDB 里，你只需要一条语句就能把它变成一张表：

CREATE TABLE ecommerce AS
SELECT * FROM read_json_auto('ecommerce_data.json');

read_json_auto 会自动扫描文件，推断出所有字段的类型，包括嵌套对象和数组。

你不用手动定义任何 schema。执行 SELECT * FROM ecommerce; 就能看到数据已经整整齐齐地躺在表里了。

注：ecommerce_data.json 这个文件文章末尾提供下载链接。（其实就是一些简单的数据，你也可以直接用自己已有的 JSON 文件来测试）

基本查询

现在，你想知道一共有多少订单，以及每个订单的客户叫什么。这就像查普通数据库一样简单：

SELECT COUNT(*) AS order_count FROM ecommerce;

SELECT order_id, customer->>'name' AS customer_name FROM ecommerce;

这里用到了 ->> 操作符，它从 JSON 中提取字段并返回文本。

如果只想返回 JSON 类型，可以用 ->。

比如 customer->'name' 返回的是 JSON 字符串，而 customer->>'name' 返回的是纯文本。

日常分析中，->> 更常用，因为可以直接用于比较和展示。

挖出嵌套里的秘密

JSON 的嵌套结构往往是分析中最头疼的部分。

比如，你想知道客户都来自哪些城市，或者找出西雅图的客户。

用链式箭头操作符，可以一层层深入：

SELECT
  order_id,
  customer->>'name' AS customer_name,
  customer->'address'->>'city' AS city,
  customer->'address'->>'state' AS state
FROM ecommerce;

SELECT order_id, customer->>'name' AS customer_name
FROM ecommerce
WHERE customer->'address'->>'city' = '北京';

支付信息同样可以这样提取。

注意，payment->>'total' 出来的是文本，如果要计算总销售额，需要先用 CAST 转成数值：

SELECT
  order_id,
  payment->>'method' AS payment_method,
  CAST(payment->>'total' AS DECIMAL) AS total_amount
FROM ecommerce;

-- 计算总销售额
SELECT SUM(CAST(payment->>'total' AS DECIMAL)) AS total_revenue
FROM ecommerce;

这些查询让你不用写一行 Python，就能回答“客户分布在哪些城市”“哪种支付方式最流行”“这个月总收入多少”等问题。

拆开数组，看看里面有什么

订单里的 items 是一个数组，每个元素是一个商品对象。要分析商品，就得先把数组展开。DuckDB 提供了 unnest() 函数，它能把数组变成多行，每个元素一行：

SELECT
  order_id,
  customer->>'name' AS customer_name,
  unnest(items) AS item
FROM ecommerce;

这样，每个订单里的每个商品都变成了独立的一行。

接着，我们可以从展开后的 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 item
  FROM ecommerce
) AS unnested_items;

有了这个结果，你就可以做各种聚合分析了。

比如，按商品类别计算平均价格：

SELECT
  item->>'category' AS category,
  AVG(CAST(item->>'price' AS DECIMAL)) AS avg_price
FROM (
  SELECT unnest(items) AS item FROM ecommerce
) AS unnested_items
GROUP BY category
ORDER BY avg_price DESC;

如果你只想知道每个订单包含多少个商品，不需要展开数组，直接用 json_array_length()：

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;

这些分析在电商场景下非常实用：哪个品类最贵？每个订单平均买几件？高价值订单有什么特征？全部可以用 SQL 搞定。

这些技巧还能用在哪？

DuckDB + SQL 的组合远不止电商订单。你可以用它来分析：

• API 响应日志：比如从天气 API 抓取的 JSON，快速统计某个月份的平均气温。
• 应用导出数据：比如你的健身记录、音乐收听历史，很多 App 都支持导出 JSON。
• 服务器日志：JSON 格式的日志文件，用 SQL 过滤错误、统计访问量。
• 配置文件：批量检查成百上千个 JSON 配置文件中的某个字段。

它的优势在于：无需编写解析代码，无需搭建数据库，直接对文件执行 SQL。

对于探索性数据分析来说，这简直是效率神器。

总结

下次当你面对一堆嵌套 JSON 感到无从下手时，别急着打开 Python 或 Excel。

试试 DuckDB，打开终端，几行 SQL 就能让你看清数据背后的故事。

文中用到的 JSON 数据文件：ecommerce_data.json: https://url11.ctfile.com/f/45455611-17569896565945-1fcdf4?p=6872 (访问密码: 6872)