Oracle 12.1中JSON_TABLE嵌套查询的FOR ORDINALITY列异常行为

你遇到的这个问题确实是Oracle 12.1版本的已知Bug，并非单纯的大版本功能差异，而是早期JSON处理模块的实现缺陷导致的。

问题本质 #

在Oracle 12.1中，当JSON_TABLE使用NESTED PATH嵌套解析JSON数组时，FOR ORDINALITY生成的序号没有正确关联到父层级的对象，而是对所有返回的行进行全局计数。比如你的例子中，原本应该是每个父对象对应2行结果，u_lvl分别为1、1、2、2，但12.1会返回1、2、3、4，完全不符合预期。

而Oracle 12.2版本对JSON功能做了大量修复和增强，其中就包括修正了FOR ORDINALITY在嵌套路径下的计数逻辑，所以在12.2+版本中执行相同查询会得到正确的层级序号。

Oracle LiveSQL的特殊情况 #

LiveSQL通常运行的是Oracle 19c或更高版本的环境，但如果你遇到了不同的结果，大概率是测试时的细节差异（比如JSON数据格式是否完全一致、查询语句是否有细微调整），而非版本本身的问题——高版本Oracle已经完全修复了这个序号异常的Bug。

能否通过数据库设置调整？ #

很遗憾，没有任何数据库深层配置可以修改FOR ORDINALITY的这个行为，因为这是核心功能的实现缺陷，并非可配置的参数项。

替代解决方案（无法升级版本时） #

如果你暂时无法升级到12.2+版本，可以通过窗口函数手动重新计算父层级序号。比如针对你的场景，每个父对象对应2条子表行，你可以用以下查询修正u_lvl：

SELECT 
    -- 按原始序号分组，每2行对应一个父层级序号
    CEIL(jt.u_lvl_original / 2) AS u_lvl,
    jt.debitOverturn,
    jt.l_lvl,
    jt.debit,
    jt.credit
FROM (
    -- 原查询，保留原始异常的u_lvl作为临时字段
    SELECT 
        jt.u_lvl AS u_lvl_original,
        jt.debitOverturn,
        jt.l_lvl,
        jt.debit,
        jt.credit
    FROM test1 s,
    JSON_TABLE (
        s.json_data,'$[*]'
        COLUMNS (
            u_lvl FOR ORDINALITY,
            debitOverturn VARCHAR2(20) PATH '$.debitOverturn',
            NESTED PATH '$.table[*]' COLUMNS (
                l_lvl FOR ORDINALITY,
                debit VARCHAR2(38) PATH '$.debit',
                credit VARCHAR2(38) PATH '$.credit'
            )
        ) AS jt
    WHERE s.id = 1
) jt;

如果父对象对应的子表行数不固定，你可以用DENSE_RANK()结合父层级的唯一标识（比如debitOverturn，如果它在每个父对象中是唯一的）来生成正确序号：

SELECT 
    DENSE_RANK() OVER (ORDER BY jt.debitOverturn) AS u_lvl,
    jt.debitOverturn,
    jt.l_lvl,
    jt.debit,
    jt.credit
FROM (
    -- 原查询
    SELECT 
        jt.u_lvl AS u_lvl_original,
        jt.debitOverturn,
        jt.l_lvl,
        jt.debit,
        jt.credit
    FROM test1 s,
    JSON_TABLE (
        s.json_data,'$[*]'
        COLUMNS (
            u_lvl FOR ORDINALITY,
            debitOverturn VARCHAR2(20) PATH '$.debitOverturn',
            NESTED PATH '$.table[*]' COLUMNS (
                l_lvl FOR ORDINALITY,
                debit VARCHAR2(38) PATH '$.debit',
                credit VARCHAR2(38) PATH '$.credit'
            )
        ) AS jt
    WHERE s.id = 1
) jt;

总结 #

• 这是Oracle 12.1的专属Bug，12.2及以上版本已修复；
• 无数据库配置项可调整该行为，升级版本是彻底解决的最佳方案；
• 无法升级时，可通过窗口函数手动修正序号。

内容的提问来源于stack exchange，提问作者caine