MySQL JSON 查询数组对象 怎样处理

在 MySQL 8.4 中，查询 JSON 数组里的对象，可以用 JSON_TABLE 将每个对象映射成一行，再对这行的多个字段同时过滤。这样可以避免“SKU 在第一个对象命中、数量在第二个对象命中”，却把整张订单误判为符合条件。

下面先给最小写法，再约定订单查询接口的返回粒度、路径和异常语义。所有数据均为原创示例，结果是按 SQL 推导的预期，未在本地启动数据库执行。官方文档入口：https://dev.mysql.com/doc/refman/8.4/en/json-table-functions.html。

先把一个对象映射成一行

-- 将每个明细对象映射为一行，再要求同一行同时满足两个条件。
SELECT jt.item_no, jt.sku, jt.qty
FROM JSON_TABLE(
  '[{"sku":"INK-R","qty":1},{"sku":"PAD-S","qty":8},{"sku":"INK-R","qty":4}]',
  '$[*]' COLUMNS (
    item_no FOR ORDINALITY,
    sku VARCHAR(24) PATH '$.sku' NULL ON EMPTY ERROR ON ERROR,
    qty INT PATH '$.qty' NULL ON EMPTY ERROR ON ERROR
  )
) AS jt
WHERE jt.sku = 'INK-R' AND jt.qty >= 3
ORDER BY jt.item_no;

这条语句的预期匹配项是 item_no 为 3 的对象，SKU 为 INK-R、qty 为 4。数组第二个对象虽然数量为 8，但它的 SKU 是 PAD-S，因此不应命中。这里讨论的是“同一个数组元素同时满足条件”，不是分别检查两个数组投影是否各自存在某个值。

结构上，payload 保存 JSON 文档，items 是对象数组，JSON_TABLE 负责关系映射，sku 与 qty 是同一对象的列，item_no 标记该对象在本次展开中的位置。图中的连线表示这些字段和映射的关联，不是执行计划。

接口返回明细，还是返回订单

同一个订单可能含多个符合条件的对象，接口必须先决定结果粒度。若接口返回订单明细，保留 order_id、item_no、sku、qty，每个匹配对象各占一行；若接口只返回符合条件的订单，使用 EXISTS，让每张订单最多出现一次。不要先展开全部对象，再靠应用层碰巧去重来掩盖返回契约不清的问题。

orders 一类父表提供 order_id，JSON_TABLE 提供匹配明细；WHERE 描述明细条件，EXISTS 描述父行是否入选。这些构成查询接口的选择边界：明细列表和订单列表不能简单套用同一个分页总数。

路径、字段类型与序号要分别约定

行路径 $.items[*] 选取 items 数组内的元素；COLUMNS 内的 $.sku 和 $.qty 相对于当前元素，而不是原文档根节点。若文档根本身就是数组，才使用最小示例中的 $[*]。JSON_TABLE 后面的别名 jt 必须保留。

item_no 的 FOR ORDINALITY 从 1 开始计数，而 JSON 数组下标从 0 开始。它适合在同一订单内表达展示顺序，不适合作为长期不变的业务明细 ID；在数组前面插入一个对象后，后续位置会变化。需要修改或追踪明细时，应在对象中保存独立且稳定的 item_id。

sku 映射成 VARCHAR(24)，qty 映射成 INT，是本例接口的类型决策，不是自动证明输入原本就是这些 JSON 类型。MySQL 会进行转换；例如可转换的数字字符串可能仍进入整数列。若写入契约要求 qty 必须为 JSON 数字且为正整数，应在入口明确校验，而不能把 ERROR ON ERROR 当作完整 JSON Schema。

用户提供的 SKU 和数量阈值在应用查询中应通过驱动参数绑定，示例常量仅用于展示。路径则由服务端固定或白名单选择，不把用户输入拼接进 SQL。SKU 的大小写比较还受实际列的字符集和排序规则影响，业务若要求大小写敏感，需要在实际表和查询中显式约定。

缺字段不等于零，类型错误也不等于没匹配

本例用 NULL ON EMPTY 表示缺少字段时产生 SQL NULL，用 ERROR ON ERROR 表示出现不能按列规则处理的值时抛错。两者按 ON EMPTY 在前、ON ERROR 在后的顺序书写。qty 缺失后，qty >= 3 不成立；这只是筛选规则，不代表缺失数量被业务认可为有效输入。

-- 存在性列区分缺键与键存在；JSON null 映射为 SQL NULL。
SELECT jt.has_qty, jt.qty
FROM JSON_TABLE(
  '[{"sku":"INK-R"},{"sku":"INK-R","qty":null},{"sku":"INK-R","qty":0}]',
  '$[*]' COLUMNS (
    has_qty INT EXISTS PATH '$.qty',
    qty INT PATH '$.qty' NULL ON EMPTY ERROR ON ERROR
  )
) AS jt;

按语义推导，这三个对象分别对应“has_qty 为 0、qty 为 NULL”“has_qty 为 1、qty 为 NULL”“has_qty 为 1、qty 为 0”。因此要区分缺键与显式 JSON null，可添加 EXISTS PATH 列；仅检查 qty IS NULL 无法分清两者。

ON ERROR 不是整条语句的异常捕获器：无效 JSON 路径仍会报错，字段截断也可能产生独立警告。JSON 列能约束文档语法有效，却不会自动保证根结构、items 必为数组、每个元素必为对象或字段范围符合业务。服务端接口应把存储异常和“没有匹配记录”分开，不能把数据库错误统一包装为空列表。

兼容策略先限定版本，再考虑数据规模

本文以 MySQL 8.4 文档为语义范围，不把它直接套用到旧 MySQL、MariaDB 或其他兼容实现。接入现有服务前，应确认数据库产品和版本，而不是仅凭驱动名称推断支持情况。本文不提供未经核实的跨产品同等兼容承诺。

JSON_TABLE 解决表达方式，不自动提供针对每个派生字段的普通索引。订单量和数组长度增大时，应先用日期、租户或订单范围等父表条件缩小业务候选，并在目标环境检查执行计划；这不是保证数据库一定按书写顺序执行。如果明细查询成为高频核心能力，考虑独立关系明细表及相应索引，而不是无限扩大单行 JSON。

空数组在这里的顶层展开中没有明细行；直接展开会使无匹配父行不出现。若要保留父行，需要专门设计 LEFT JOIN 的 ON 条件，不能在 WHERE 中又用明细列过滤掉 NULL 行。为避免混淆，下面完整例子只展示明细查询与订单 EXISTS，不混入外连接。

用同一份样例对照两种返回结果

-- 在独立示例库使用新表名；不要覆盖已有业务表。
CREATE TABLE json_order_demo (
  order_id BIGINT PRIMARY KEY,
  payload JSON NOT NULL
);

-- 第一单的两个条件分别出现在不同对象中，第二单才有真正匹配的对象。
INSERT INTO json_order_demo (order_id, payload) VALUES
(7101, '{"items":[{"sku":"INK-R","qty":1},{"sku":"PAD-S","qty":8}]}'),
(7102, '{"items":[{"sku":"INK-R","qty":4},{"sku":"INK-R","qty":6}]}'),
(7103, '{"items":[]}'),
(7104, '{"items":[{"sku":"INK-R"}]}');

-- 返回匹配明细：一个订单允许有多行结果。
SELECT o.order_id, jt.item_no, jt.sku, jt.qty
FROM json_order_demo AS o
CROSS JOIN JSON_TABLE(
  o.payload,
  '$.items[*]' COLUMNS (
    item_no FOR ORDINALITY,
    sku VARCHAR(24) PATH '$.sku' NULL ON EMPTY ERROR ON ERROR,
    qty INT PATH '$.qty' NULL ON EMPTY ERROR ON ERROR
  )
) AS jt
WHERE jt.sku = 'INK-R' AND jt.qty >= 3
ORDER BY o.order_id, jt.item_no;

明细查询预期返回 7102 的两行，item_no 分别为 1、2，qty 为 4、6；7101 的两个对象各自只满足一部分条件，因此不能命中。7103 没有元素，7104 缺少 qty，都不应出现在当前筛选结果中。

-- 返回订单：子查询只回答该订单是否含匹配对象，不复制订单行。
SELECT o.order_id
FROM json_order_demo AS o
WHERE EXISTS (
  SELECT 1
  FROM JSON_TABLE(
    o.payload,
    '$.items[*]' COLUMNS (
      sku VARCHAR(24) PATH '$.sku' NULL ON EMPTY ERROR ON ERROR,
      qty INT PATH '$.qty' NULL ON EMPTY ERROR ON ERROR
    )
  ) AS jt
  WHERE jt.sku = 'INK-R' AND jt.qty >= 3
)
ORDER BY o.order_id;

订单查询预期只返回一次 7102。建议读者额外准备缺键、JSON null、无法转换的 qty、同单重复 SKU 和空数组这些输入，分别核对错误与结果粒度；本文不声称已执行回归或测得性能。

为什么 JSON_EXTRACT 后拿到的还是数组？通配路径可以匹配多个值，取出一个投影数组与把对象拆成关系行是两回事；需要跨字段联合过滤时，本例选择 JSON_TABLE。

能否直接用文本 LIKE 找 SKU？不宜把序列化文本包含关系当成结构化条件。转义、字符串片段和不同对象的字段组合都可能使判断偏离业务任务。

分页数量为什么变大了？明细展开改变了行粒度，一张订单有两个匹配对象就有两行。先决定分页对象是订单还是明细，再选择 EXISTS 或展开查询。

参考：MySQL 8.4 JSON_TABLE 官方说明。核心取舍是保留同对象字段关联，显式定义返回粒度、类型转换和空值策略。