当前位置：首页 > 文章列表 > 科技周边 > 人工智能 > MySQL JSON_TABLE 如何拆分嵌套数组：路径映射、行扩展与空值边界

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

来源：17golang原创 2026-08-29 10:38:45 0浏览收藏

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

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

要点速览

JSON_TABLE() 的每个 PATH 都相对当前行路径解析。
NESTED PATH '$.items[*]' 会把一个订单行扩展为多个商品行。
商品对象缺少 batches 时，嵌套列默认以 NULL 补行。
标量类型转换与缺失字段要分别用 ON ERROR、ON EMPTY 处理。

先固定一份可观察的订单 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;

MySQL JSON_TABLE 从 orders 经过 items 到 batches 的两层行扩展，标出 order_id、sku、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 路径。路径负责描述数据层级，过滤负责表达业务口径；分开写更容易检查“缺批次的商品是否应该保留”。

MySQL JSON_TABLE 缺少 batches 时保留父级商品行并将 batch_no 置为 NULL，随后由过滤条件决定是否保留

第二张图把这个边界压缩成四个观察点：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”。