如何用JSON_TABLE访问JSON数字标签名？PL/SQL ORA-40597报错解决

有一段作为存储过程参数传入的JSON数据，需要从中获取discountId和discountName属性，但其中的键名243431是数字类型，直接访问时出现语法错误。

JSON结构 #

"discountDetail": {
  "243431": {
    "discountId": "243431",
    "discountName": "Standard Service Discount - USD",
    "discountDescription": "Standard - Standard Service Discount - USD",
    "discountGroup": "Standard",
    "modifierLineTypeCode": "DIS"
  }
}

原PL/SQL代码及错误 #

原存储过程代码：

CREATE OR REPLACE PROCEDURE parse_json (p_json CLOB)
IS
BEGIN
    for i in (SELECT *
             FROM JSON_TABLE (
                      p_json FORMAT JSON,'$'
                      COLUMNS (
                          NESTED PATH '$.responseHeader.discountDetail.243431[*]'
                              COLUMNS (discountId VARCHAR2 PATH '$.discountId',
                                       discountName VARCHAR2 PATH '$.discountName'))) loop
        DBMS_OUTPUT.put_line ('discountId=' || i.discountId);
        DBMS_OUTPUT.put_line ('discountName=' || i.discountName);
    end loop;
END;

执行时出现错误：

[Error] Compilation (70: 15): PL/SQL: ORA-40597: JSON path expression syntax error ('$.responseHeader.discountDetail.243431[*]')
JZN-00209: Unexpected characters after end of path
at position 38

方法一：使用方括号包裹数字键名 #

在JSON路径中，数字开头的键名需要用["键名"]的形式访问，同时注意243431是单个对象而非数组，不需要加[*]。修改后的代码如下：

CREATE OR REPLACE PROCEDURE parse_json (p_json CLOB)
IS
BEGIN
    for i in (SELECT *
             FROM JSON_TABLE (
                      p_json FORMAT JSON,'$'
                      COLUMNS (
                          NESTED PATH '$.responseHeader.discountDetail["243431"]'
                              COLUMNS (discountId VARCHAR2 PATH '$.discountId',
                                       discountName VARCHAR2 PATH '$.discountName'))) loop
        DBMS_OUTPUT.put_line ('discountId=' || i.discountId);
        DBMS_OUTPUT.put_line ('discountName=' || i.discountName);
    end loop;
END;

方法二：遍历discountDetail下的所有子对象（键名不固定时） #

如果discountDetail下的数字键名是动态变化的，无法提前确定，可以用*遍历所有子对象：

CREATE OR REPLACE PROCEDURE parse_json (p_json CLOB)
IS
BEGIN
    for i in (SELECT *
             FROM JSON_TABLE (
                      p_json FORMAT JSON,'$'
                      COLUMNS (
                          NESTED PATH '$.responseHeader.discountDetail.*'
                              COLUMNS (discountId VARCHAR2 PATH '$.discountId',
                                       discountName VARCHAR2 PATH '$.discountName'))) loop
        DBMS_OUTPUT.put_line ('discountId=' || i.discountId);
        DBMS_OUTPUT.put_line ('discountName=' || i.discountName);
    end loop;
END;

方法三：使用JSON_VALUE直接提取（单个固定键场景） #

如果只需要提取单个固定键下的属性，可用JSON_VALUE简化操作：

CREATE OR REPLACE PROCEDURE parse_json (p_json CLOB)
IS
    v_discount_id VARCHAR2(50);
    v_discount_name VARCHAR2(200);
BEGIN
    v_discount_id := JSON_VALUE(p_json, '$.responseHeader.discountDetail["243431"].discountId');
    v_discount_name := JSON_VALUE(p_json, '$.responseHeader.discountDetail["243431"].discountName');
    
    DBMS_OUTPUT.put_line ('discountId=' || v_discount_id);
    DBMS_OUTPUT.put_line ('discountName=' || v_discount_name);
END;

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