使用 SQLite 的 json_each 将 JSON 数组展开为行 — 按商品汇总订单明细

关于本文
本文是通过利用生成式 AI 的自动化生成流程创建的。已核实 SQLite 官方 JSON 函数规范，且示例数据为虚拟订单。

验证情况：已核实官方规范 / 尚未在 SQLite 实机上运行验证
信息核实日期：2026年10月11日。以下输出为预期示例。

如果 JSON 数组包含在单个单元格中，使用普通的 SQL 很难按商品进行汇总。借助 SQLite 的 json_each()，可以将数组的每个元素展开为虚拟行，并通过 SUM() 或 GROUP BY 进行汇总。无需更新原始数据。

在此，我们假设订单表的 items 列中包含“商品名称、数量、单价”的数组。我们将提取各个订单的明细，计算各商品的销售额，并确认 JSON 损坏或为空数组时的处理方式。

将 JSON 数组展开为行的原因

例如，当一个订单包含两件商品时，JSON 结构如下所示。

[
  {"sku":"pen","qty":2,"price":120},
  {"sku":"note","qty":1,"price":350}
]

虽然这种结构对人类阅读很友好，但作为 SQL 中的普通列，sku 和 qty 并不是独立的。用逗号分割字符串的方法无法应对 JSON 的引号、转义和嵌套，因此应当避免。

json_each(items) 是一个表值函数，用于逐条返回 JSON 数组直接子级的元素。如果每个元素都是一个 JSON 对象，返回的 value 针对 json_extract(value, '$.sku') 这样提取所需的值。

flowchart LR
    A["orders: 1行にitems配列"] --> B["json_each(items)"]
    B --> C["商品明細: 1要素=1行"]
    C --> D["json_extractでsku/qty/price取得"]
    D --> E["GROUP BYとSUMで集計"]

此转换仅在 SELECT 期间进行处理。它不会拆分并重新保存原始 JSON 或表的内容。

准备工作：创建虚构订单

自 SQLite 3.38.0 起，JSON 函数已作为标准内置。但是，在构建时指定了 SQLITE_OMIT_JSON 的配置除外。以下是在 SQLite 命令行客户端中执行的 SQL。

CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    items TEXT NOT NULL
);

INSERT INTO orders (order_id, items) VALUES
(101, '[{"sku":"pen","qty":2,"price":120},{"sku":"note","qty":1,"price":350}]'),
(102, '[{"sku":"pen","qty":3,"price":120}]'),
(103, '[]');

TEMP TABLE 使用它的目的是避免将练习数据混入现有的业务表中。请在新的空 SQLite 数据库中执行。断开连接后，该表将消失。

订单 101 包含 2 支笔和 1 本笔记本，102 包含 3 支笔，103 没有商品。数量和单价均为整数，不处理税费、折扣和退货。

首先展开并观察 1 条记录

SELECT
    o.order_id,
    j.key AS array_index,
    json_extract(j.value, '$.sku') AS sku,
    json_extract(j.value, '$.qty') AS qty,
    json_extract(j.value, '$.price') AS price
FROM orders AS o
JOIN json_each(o.items) AS j
WHERE o.order_id = 101
ORDER BY j.key;

预计会从订单 101 中返回 2 行。数组下标 key 分别为 0 和 1，sku 分别是 pen 和 note。json_each() 用于遍历数组的元素，json_extract() 从元素中指定的路径提取值。

这里的 JOIN 的作用是将订单各行中包含的 JSON 展开为对应于该行的元素集合。在 SQL 中，json_each(o.items) 引用了左侧的 o.items。

JSON为空数组的订单103，json_each()由于不返回任何元素，因此不会出现在此JOIN的结果中。如果想保留没有商品的订单，则需要用到下文提到的LEFT JOIN。

按商品汇总销售额

将展开后的元素数量与单价相乘，并按商品代码求出其总计。

SELECT
    json_extract(j.value, '$.sku') AS sku,
    SUM(
        json_extract(j.value, '$.qty') *
        json_extract(j.value, '$.price')
    ) AS sales
FROM orders AS o
JOIN json_each(o.items) AS j
GROUP BY json_extract(j.value, '$.sku')
ORDER BY sku;

期望结果是 note = 350、pen = 600 。圆珠笔在订单101中有2支，在102中有3支，共计5支，单价120，因此为600。

此处假设数量和单价均为数值。实际的JSON中可能会混入 "qty":"2" 这样的字符串、null、字段缺失、负数等情况。SQLite虽然有类型转换规则，但在实际业务的金额计算中依赖隐式转换是非常危险的。

在实际工作中，应提前规定好如 json_type(j.value,'$.qty')='integer' 这样的类型检查，以及数量是否大于等于0、单价是否在允许范围内等条件。对于小数金额，为避免舍入误差，也请考虑将货币最小单位作为整数处理的设计方案。

将空数组订单也保留在列表中

当按订单单位查询数量时，如果删除了空数组，就无法区分是“没有该订单”还是“商品数量为0”。

SELECT
    o.order_id,
    COUNT(j.key) AS item_count
FROM orders AS o
LEFT JOIN json_each(o.items) AS j
GROUP BY o.order_id
ORDER BY o.order_id;

期望结果是订单101为2，102为1，103为0。LEFT JOIN 即使没有匹配的元素也会保留左侧的订单。COUNT(*) 如果设为 ，即便是空数组也会计算LEFT JOIN生成的行数而变成1，因此这里使用 COUNT(j.key) 。

此外，item_count 是商品种类元素的数量。qty 这与总数不同。订单 101 有 2 个明细，但数量总计为 3。请区分您具体需要哪一个。

遇到损坏的 JSON 时该如何处理

如果输入不是合法的 JSON，JSON 函数可能会报错。应避免无条件地将从外部系统接收到的字符串传递给 json_each()。

首先应以只读方式进行检查。

SELECT order_id, json_valid(items) AS valid_json
FROM orders
ORDER BY order_id;

本次的 3 条记录预期都为 1。json_valid() 可用于验证普通 JSON 字符串的有效性。然而，即使是合法的 JSON，其顶级元素也不一定非得是数组。

如果只想展开数组，可以采用以下方法先将输入标准化，然后再传递给函数。

SELECT
    o.order_id,
    j.key,
    j.value
FROM orders AS o
JOIN json_each(
    CASE
        WHEN json_valid(o.items) AND json_type(o.items) = 'array'
        THEN o.items
        ELSE '[]'
    END
) AS j;

由于非法的 JSON 或非数组的值会被当作空数组处理，因此可以继续执行聚合处理。不过，这并不意味着可以把非法数据视为正常数据。在实际业务中，需要单独提取出异常记录，调查原因，并记录被排除在处理范围之外的数量。

json_each 和 json_tree 有什么区别？

json_each() 返回指定数组或对象直接下属的元素。它非常适合处理类似本次“数组中的每个商品”这种用途。另一方面，json_tree() 会递归遍历所有子孙节点，因此当您需要搜索嵌套较深的值时非常有用。

如果直接用 json_tree() 来展开深层结构，也会将对象自身的行和中间节点包含进来。对于只想统计商品行的处理来说，可能会混入多余的行。请首先确定所需的层级，如果只需直接下属层级，则选择 json_each()。

json_extract() 即使只用 $[0].sku 也可以获取特定位置的值，例如第 0 个或第 1 个。但是，如果数组的长度发生变化，手写索引值的方法将难以维护。如果数组长度不确定，则应使用 json_each() 会更自然。

运维注意事项与设计决策

将 JSON 保存在列中本身并没有错。 如果是来自外部 API 的灵活属性，或是偶尔引用的附加信息，这样很方便。但是，如果需要频繁聚合订单明细，并施加搜索条件或完整性约束，那么将明细规范化到单独的表中可能会让设计更容易。

每次展开 JSON 都会产生解析成本。对于大量数据，可以考虑将搜索目标字段作为常规列或生成列保留、使用索引以及分离为明细表。实际速度取决于数据量、JSON 大小、SQL 和 SQLite 版本，因此请EXPLAIN QUERY PLAN 并通过实际测量来判断。

如果只看 SELECT，本次的 SQL 是只读的，但用于练习的 CREATE TEMP TABLE 和 INSERT 会创建临时表。请不要在生产数据库上进行练习，而应该在空的测试数据库中尝试。

最后，添加 INSERT INTO orders VALUES (104, '[{"sku":"note","qty":2,"price":350}]'); 并再次按商品汇总销售额。笔记本的销售额应该从 350 变为 1050。通过亲自确认这一变化，您将理解将 JSON 数组转换为行然后再进行汇总的流程。

官方文档

• SQLite JSON 函数与运算符 — json_each、json_extract、json_valid、json_tree 及内置条件

• SQLite SELECT — JOIN、GROUP BY 和聚合基础

• SQLite 聚合函数 — COUNT、SUM 的运作方式

本文中的 SQL 是基于 SQLite 官方规范的教程。由于未在 SQLite 真实设备上进行执行确认，所发布的数值是从示例数据计算出的期望值。