MySQL JSON_TABLE 的 FOR ORDINALITY 怎么保留数组原始序号

要保留 MySQL JSON 数组元素的位置，最直接的写法是在 JSON_TABLE() 的 COLUMNS 中声明一个 FOR ORDINALITY 列，并让行路径使用 '$[*]'。它会为展开后的每一行生成从 1 开始的序号；如果应用需要零基索引，再在查询结果中减 1。

关键边界是：序号统计的是当前 COLUMNS 的行源，不是 JSON 文本里永远固定的下标。想保留原始数组位置时，先完整展开，再用 SQL 条件过滤，不要先把路径改成只匹配部分元素。

一、用 $[*] 展开数组并声明 FOR ORDINALITY

假设接口把商品明细放在 JSON 数组中，需要把数组位置一起落到查询结果。row_no FOR ORDINALITY 不需要再写数据类型或 PATH，它负责对当前行源逐行编号。

-- $[*] 让数组每个元素成为一行，row_no 从 1 开始
SET @payload = '[{"sku":"A-10","qty":2},{"sku":"B-20","qty":5},{"sku":"C-30","qty":1}]';

SELECT jt.row_no, jt.sku, jt.qty
FROM JSON_TABLE(
    @payload,
    '$[*]' COLUMNS (
        row_no FOR ORDINALITY,
        sku VARCHAR(32) PATH '$.sku',
        qty INT PATH '$.qty'
    )
) AS jt;

结果中的 row_no 依次是 1、2、3。它表示当前数组行源中的位置，不是业务主键，也不会因为 sku 相同就合并行。需要和应用数组下标对齐时，可以输出 CAST(row_no AS SIGNED) - 1，避免直接对无符号值做减法时产生边界误解。

二、在嵌套 COLUMNS 中分别记录父子序号

JSON 经常是“订单数组下还有明细数组”。这时不要只保留一个序号：顶层 order_no 标识第几个订单，嵌套层的 item_no 标识该订单里的第几个明细。两个 FOR ORDINALITY 位于不同的 COLUMNS 作用域，组合起来才能定位一条明细。

-- 父级和子级各自编号，组合键可定位具体明细
SET @orders = '[
  {"order_id":101,"items":[{"name":"键盘","qty":1},{"name":"鼠标","qty":2}]},
  {"order_id":102,"items":[{"name":"耳机","qty":1}]}
]';

SELECT order_no, order_id, item_no, item_name, qty
FROM JSON_TABLE(
    @orders,
    '$[*]' COLUMNS (
        order_no FOR ORDINALITY,
        order_id INT PATH '$.order_id',
        NESTED PATH '$.items[*]' COLUMNS (
            item_no FOR ORDINALITY,
            item_name VARCHAR(64) PATH '$.name',
            qty INT PATH '$.qty'
        )
    )
) AS jt;

第一笔订单的明细序号是 1、2，第二笔订单重新从 1 开始。不要把 item_no 当成整个文档的全局序号；如果业务需要全局位置，应另外定义跨层组合规则或保存原始 JSON 路径。

三、根据业务需要转换零基索引并安全取值

数据库结果通常更适合展示一基序号，因为读者看到的“第 1 项”就是 1；JavaScript、Go 切片等应用容器常用零基下标。建议同时保留原始的 row_no，只在输出层派生 array_index，这样排查数据时不会丢掉 MySQL 的原始计数语义。

-- 保留一基序号，同时派生应用侧常用的零基下标
SELECT
    jt.row_no,
    CAST(jt.row_no AS SIGNED) - 1 AS array_index,
    jt.sku,
    jt.qty
FROM JSON_TABLE(
    @payload,
    '$[*]' COLUMNS (
        row_no FOR ORDINALITY,
        sku VARCHAR(32) PATH '$.sku',
        qty INT PATH '$.qty' DEFAULT '0' ON EMPTY
    )
) AS jt;

字段缺失时，DEFAULT '0' ON EMPTY 只处理路径没有值的情况；它和序号没有关系。序号列本身由行源产生，不要为它额外写 PATH。对象或数组被写入标量列时，还要按需要选择 ON ERROR 策略。

四、过滤时避免把新序号误当成原始位置

如果只想留下数量大于 1 的元素，应该先用 '$[*]' 展开，再在外层 WHERE 过滤。这样第二个数组元素即使被过滤掉，第三个元素仍会保留它原来的 row_no = 3。

-- 先生成原始位置，再过滤；row_no 不会因删行而重排
SELECT row_no, sku, qty
FROM JSON_TABLE(
    @payload,
    '$[*]' COLUMNS (
        row_no FOR ORDINALITY,
        sku VARCHAR(32) PATH '$.sku',
        qty INT PATH '$.qty'
    )
) AS jt
WHERE qty > 1
ORDER BY row_no;

相反，如果把行路径改成只匹配某一部分数组元素，FOR ORDINALITY 看到的就是这个新行源，序号可能从 1 重新计数。因此要先确定需求：是“筛选后的结果序号”，还是“原始 JSON 数组下标”。前者可以直接使用筛选后的行源，后者应全量展开后过滤，并保留序号列。

相关问题

FOR ORDINALITY 的序号从 0 还是从 1 开始？

从 1 开始。对接零基数组时，保留原列并在外层用有符号表达式减 1。

嵌套数组的 item_no 为什么每个父对象都从 1 开始？

因为它属于嵌套 COLUMNS 的独立计数作用域。要定位明细，应同时记录父级序号或父级业务 ID。

过滤后想保留原来的数组位置怎么办？

用 $[*] 完整展开，在关系结果上使用 WHERE 过滤，并保留 FOR ORDINALITY 列；不要把筛选后的新行源序号当成原始下标。