MySQL JSON_TABLE 如何拆分嵌套数组：路径映射、行扩展与空值边界

订单服务把商品明细暂存在 JSON 列里时，真正麻烦的通常不是取出一个字段，而是把“一个订单—多个商品—多个批次”拆成能过滤、统计和追查的行。MySQL 的 JSON_TABLE() 可以完成这次展开，但路径写错时，结果会悄悄少行，空数组也可能让人误以为数据丢了。

先用顶层 '$[*]' 生成订单行，再用相对父路径的 NESTED PATH 展开商品和批次；没有匹配项时，MySQL 会补出一行 NULL，是否保留它要由查询条件决定。

先固定一份可观察的订单 JSON

下面的示例故意保留两个边界：订单 1002 没有 batches，订单 1003 的商品数组为空。这样能直接看出“没有匹配”和“有匹配但值为空”不是一回事。

SET @orders = '[
  {"order_id":1001,"items":[
    {"sku":"A-10","qty":2,"batches":[{"no":"B01","qty":1},{"no":"B02","qty":1}]},
    {"sku":"B-20","qty":1,"batches":[{"no":"B09","qty":1}]}
  ]},
  {"order_id":1002,"items":[{"sku":"C-30","qty":4}]},
  {"order_id":1003,"items":[]}
]';

这里的 items 是订单对象的数组，batches 又是商品对象的数组。查询要做的是把父级的 order_id 带到每个商品行，再把 sku 带到每个批次行。

用 NESTED PATH 完成两层行扩展

顶层路径 '$[*]' 先选中每个订单对象；第一个 NESTED PATH '$.items[*]' 相对订单对象展开商品；第二个嵌套路径则相对商品对象展开 batches。短标签只用于说明链路，字段名仍以 SQL 为准。

SELECT jt.order_id, jt.sku, jt.item_qty, jt.batch_no, jt.batch_qty
FROM JSON_TABLE(
  @orders,
  '$[*]' COLUMNS (
    order_id INT PATH '$.order_id',
    NESTED PATH '$.items[*]' COLUMNS (
      sku VARCHAR(20) PATH '$.sku',
      item_qty INT PATH '$.qty',
      NESTED PATH '$.batches[*]' COLUMNS (
        batch_no VARCHAR(20) PATH '$.no',
        batch_qty INT PATH '$.qty'
      )
    )
  )
) AS jt
ORDER BY jt.order_id, jt.sku, jt.batch_no;

图中的 orders、items、batches 和 row expansion 是同一条数据路径的四个观察点：从父对象进入数组，再落成关系行。

对订单 1001，A-10 产生两行、B-20 产生一行；父列 order_id 会复制到展开后的子行。订单 1002 没有批次时，仍可能出现一行商品记录，只是 batch_no 和 batch_qty 为 NULL。

为什么空数组会补 NULL 行

NESTED PATH 没有匹配项时，MySQL 会为嵌套列生成 NULL-complemented row。这是为了保留父级行，行为更接近外连接；如果业务只要“确实有批次”的结果，再显式过滤 batch_no IS NOT NULL。

-- 保留订单与商品，即使商品没有批次
SELECT order_id, sku, batch_no
FROM (...上面的 JSON_TABLE...) AS jt;

-- 只统计实际批次
SELECT order_id, sku, batch_no, batch_qty
FROM (...上面的 JSON_TABLE...) AS jt
WHERE batch_no IS NOT NULL;

别把 WHERE batch_no IS NOT NULL 提前写进 JSON 路径。路径负责描述数据层级，过滤负责表达业务口径；分开写更容易检查“缺批次的商品是否应该保留”。

第二张图把这个边界压缩成四个观察点：parent row 进入 NESTED PATH，缺少子数组时得到 NULL，最后交给 filter 决定是否保留。

ON EMPTY 与 ON ERROR 要分开判断

字段不存在是 empty，找到字段但值无法转换成目标类型则是 error。比如 qty 缺失时可以给默认值；qty 是数组却映射到 INT 时，应该让错误暴露，避免把坏数据当成正常数量。

SELECT jt.sku, jt.item_qty
FROM JSON_TABLE(
  @orders,
  '$[*]' COLUMNS (
    NESTED PATH '$.items[*]' COLUMNS (
      sku VARCHAR(20) PATH '$.sku' ERROR ON EMPTY,
      item_qty INT PATH '$.qty'
        DEFAULT '0' ON EMPTY
        ERROR ON ERROR
    )
  )
) AS jt;

生产查询中建议显式写出这两个选项，并在测试数据里覆盖缺字段、空值、字符串数字和数组误填四种情况。MySQL 8.0.20 起，ON ERROR 放在 ON EMPTY 前面已不推荐，书写顺序保持“先 empty、后 error”。

两个容易误判的结果

父列为什么重复

这是行展开的必然结果：父级订单不是被复制成多份 JSON，而是作为关系结果中的关联列出现在每个子行。统计订单数时用 COUNT(DISTINCT order_id)，统计批次数量时再按 batch_no 口径聚合。

兄弟 NESTED PATH 为什么不是笛卡尔积

同一层的两个兄弟嵌套路径会依次产出记录，另一条路径的列在当前记录中置为 NULL，总行数是各路径行数之和，而不是两者相乘。需要成对组合时，应先分别展开成派生表，再按明确键连接。

一份可复用的检查清单

• 先确认每个 PATH 是相对顶层对象还是相对父级 NESTED PATH。
• 用一个空数组和一个缺字段对象验证 NULL 补行是否符合业务口径。
• 对数量、金额等字段同时测试 ON EMPTY 与 ON ERROR。
• 统计父级实体时避免直接对展开后的父列做 COUNT(*)。

相关问题

JSON_TABLE 能直接修改原 JSON 吗？

不能。它返回的是关系结果集；要修改 JSON 列，应使用 JSON 修改函数或在应用层完成写回。

空的 items 数组一定会产生商品行吗？

不会凭空产生商品值。父级是否保留、嵌套列是否补 NULL，要结合所在层级和查询过滤条件观察。

什么时候应该把 JSON 拆成真实子表？

当商品、批次需要高频筛选、关联、约束或增量更新时，真实子表通常更易维护；JSON_TABLE 更适合边界明确的导入、清洗和一次性展开。

把路径和业务口径一起验收

JSON_TABLE 的关键不是记住一段语法，而是把每一级数组对应到一组结果行，再明确空数组、缺字段和坏类型各自的处理方式。先用小样本核对 order_id → sku → batch_no 的行数，再把过滤和聚合加回正式查询，问题通常会在上线前暴露。