How to access numeric object or tag name in json using JSON_TABLE?

Question

I have below JSON which is coming as in parameter in procedure. I want to access discountid and discountname attributes from the JSON. Since 243431 is numeric tag, I am not able to access any attribute inside it.

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

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;

Getting below error when accessing 243431 tag

[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

Any alternate for this?

Answer 1

Use double quotes around the identifier (and don't forget the closing ) brackets):

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;
/

Then:

BEGIN
  DBMS_OUTPUT.ENABLE();
  parse_json('{"responseHeader":{"discountDetail": {
  "243431": {
    "discountId": "243431",
    "discountName": "Standard Service Discount - USD",
    "discountDescription": "Standard - Standard Service Discount - USD",
    "discountGroup": "Standard",
    "modifierLineTypeCode": "DIS"
  }
}}}');
END;
/

Outputs:

discountId=243431
discountName=Standard Service Discount - USD

fiddle