用 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 item

  FROM 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 item

  FROM 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 数据集时，希望你能跳过繁琐的解析步骤，直奔高效分析！