MySQL 8.4 JSON_TABLE 怎么展开事件数组：列定义、异常策略与行数验收

订单表里有一列 events，每条记录保存着支付、发货、退款等事件数组。最开始用 JSON_EXTRACT() 取单个字段还能应付，等到做报表和数据补偿任务的时候，真正头疼的场景是要把单条订单记录拆成多行事件、事件缺字段不能直接误判、脏数据又不能悄无声息被吞掉。

MySQL 8.4 自带的 JSON_TABLE() 刚好适配这类边界场景：它可以把JSON文档直接投影成能直接放到 FROM 子句里的关系表，后续通过列定义就能指定元素序号、字段类型和对应的异常处理策略。

先把一条订单拆成多条事件行

先准备一个足够简单的样例，优先确认展开后的行数和各列含义，不用一开始就把查询写得和生产级报表一样复杂。

CREATE TEMPORARY TABLE order_events_demo (
  order_id BIGINT PRIMARY KEY,
  events JSON NOT NULL
);

INSERT INTO order_events_demo VALUES
(9001, '[{"type":"paid","at":"2026-08-21 09:10:00","amount":199},{"type":"shipped","at":"2026-08-21 11:30:00"}]'),
(9002, '[{"type":"paid","at":"2026-08-21 10:00:00","amount":88}]');

SELECT o.order_id, e.seq, e.event_type, e.event_at, e.amount
FROM order_events_demo AS o
JOIN JSON_TABLE(
  o.events,
  '$[*]' COLUMNS (
    seq FOR ORDINALITY,
    event_type VARCHAR(32) PATH '$.type' ERROR ON EMPTY,
    event_at DATETIME PATH '$.at' ERROR ON EMPTY,
    amount DECIMAL(10,2) PATH '$.amount' NULL ON EMPTY NULL ON ERROR
  )
) AS e ON TRUE
ORDER BY o.order_id, e.seq;

查询结果应该返回三行：订单9001对应两行事件，订单9002对应一行事件。seq 从1开始计数，只表示当前JSON数组内的元素位置，不是全订单全局的事件ID。如果后续要做幂等写入，还是要保留原业务自带的事件标识或者事件时间字段。

列定义决定缺失值和异常值的处理逻辑

最容易搞混的就是空值和错误值的边界。$.amount 在发货事件里不存在，属于路径没有匹配到的情况；如果金额字段被写成字符串 "unknown"，再转成 DECIMAL 就属于类型转换问题。这两类场景的处理策略不能直接混成同一个默认值。

SELECT e.seq, e.amount, e.has_amount
FROM order_events_demo AS o
JOIN JSON_TABLE(
  o.events,
  '$[*]' COLUMNS (
    seq FOR ORDINALITY,
    amount DECIMAL(10,2) PATH '$.amount' NULL ON EMPTY NULL ON ERROR,
    has_amount TINYINT EXISTS PATH '$.amount'
  )
) AS e ON TRUE;

has_amount 判断的不是金额是否非零，而是对应的字段路径是否真实存在。这个标记字段很适合放在中间清洗表里，避免后续报表把“缺字段”和“金额为NULL”归为同一种数据异常。

处理嵌套数组要留意自动补出的NULL行

如果每个事件下面还挂载了 actors 数组，可以用 NESTED PATH 投影生成对应的子行。当订单事件没有绑定操作者的时候，MySQL可能会为父事件自动补出一行子列全为NULL的结果，这不是凭空多生成了一个不存在的操作者，而是外层父事件本身的信息仍然保留。

CREATE TEMPORARY TABLE order_events_nested (
  order_id BIGINT PRIMARY KEY,
  events JSON NOT NULL
);

INSERT INTO order_events_nested VALUES
(9003, '[{"type":"paid","actors":[{"id":11},{"id":12}]},{"type":"shipped"}]');

SELECT o.order_id, e.seq, e.event_type, e.actor_seq, e.actor_id
FROM order_events_demo AS o
JOIN JSON_TABLE(
  o.events,
  '$[*]' COLUMNS (
    seq FOR ORDINALITY,
    event_type VARCHAR(32) PATH '$.type',
    NESTED PATH '$.actors[*]' COLUMNS (
      actor_seq FOR ORDINALITY,
      actor_id BIGINT PATH '$.id' NULL ON EMPTY
    )
  )
) AS e ON TRUE;

写这类查询的时候要把示例用的演示表名替换成自己业务里的 order_events_nested。同一个 JSON_TABLE() 内的 NESTED PATH 会同时保留父事件的所有列和子操作者的所有列；验收的时候不要只看总行数 COUNT(*)，还要分别统计事件序号、操作者序号以及非NULL的操作者实际数量。

用三组校验确认展开过程没有悄悄丢数据

校验顺序很清晰：先核对原JSON里的预期数组长度，再核对展开后的实际行数，最后查看生成的警告记录。正式跑批处理任务的时候，建议把原始订单ID和数组元素序号一起写入临时暂存表，出现异常可以直接回溯回原始JSON内容排查。

-- 1. 原始数组长度
SELECT order_id, JSON_LENGTH(events, '$') AS expected_rows
FROM order_events_demo;

-- 2. 展开行数
SELECT o.order_id, COUNT(*) AS actual_rows
FROM order_events_demo AS o
JOIN JSON_TABLE(o.events, '$[*]' COLUMNS (seq FOR ORDINALITY)) AS e ON TRUE
GROUP BY o.order_id;

-- 3. 类型转换或截断后的警告
SHOW WARNINGS;

如果 expected_rows 和 actual_rows 不一致，先检查目标JSON文档是不是真的数组类型，以及路径是不是误写成了 $[*]。如果行数对得上但是 SHOW WARNINGS 有异常记录，说明数据已经成功展开，只是这批数据不符合直接流入金额、时间这类核心业务字段最终表的要求。

常见问题

JSON_TABLE() 能直接替代 JSON_EXTRACT() 吗？

不能直接简单替换。只取单个标量字段的时候用 JSON_EXTRACT() 更轻便；需要把数组或者嵌套对象转成多行，同时自定义字段类型和异常处理策略的场景，用 JSON_TABLE() 更合适。

ON EMPTY 和 ON ERROR 的顺序可以交换吗？

建议固定用 ON EMPTY 在前、ON ERROR 在后的顺序。MySQL官方文档和相关兼容性工作项都把反过来的顺序标记为不推荐写法，后续版本迁移的时候还可能触发额外警告。

数组没有子元素的时候为什么仍然返回一行？

嵌套路径没有匹配到内容的时候，JSON_TABLE() 会保留父行的所有数据，子列自动用NULL补齐。可以根据业务需要过滤掉子列为NULL的结果，也可以分开统计父事件总数量和子元素总数量。

NULL ON ERROR 是不是最安全的默认策略？

不是。它能保证查询不会直接中断，但也可能把格式错误的金额、错误的日期直接转成NULL藏起来。关键字段更适合先用 ERROR ON ERROR 把数据质量问题直接暴露出来，在数据清洗层确认好规则之后再选合适的默认值。

把验收结果留在暂存表里

JSON_TABLE() 的价值不只是少写几次重复的 JSON_EXTRACT()，而是把“数组元素转换成关系行”的全部逻辑都写成可核对的明确定义。生产批处理流程里，保留 order_id、数组元素序号、字段存在性标记和原始JSON摘要，再用预期行数、实际行数、警告记录三项作为校验门禁，后续重跑和数据排查的时候能省很多事。