skip to Main Content

I try to get information from a JSON file, described in the following select statement. This works in Oracle 19 and also on Oracle 12.2.0.1

with xmldata as (
  select '{
  "metaData": {
    "validForClearingDay": "2022-11-16",
    "createdStamp": "2022-11-15T16:30:17.329433+01:00"
  },
  "entries": [
    {
      "group": "01",
      "iid": 100,
      "branchId": "0000",
      "sicIid": "001008"
    },
    {
      "group": "01",
      "iid": 110,
      "branchId": "0000",
      "sicIid": "001100"
    }
  ]
}' data from dual)
select y.* from xmldata x,
  JSON_TABLE(x.data,
          '$' COLUMNS(
            validForClearingDay VARCHAR2(100) PATH '$.metaData.validForClearingDay',
            NESTED PATH '$.entries[*]'
            COLUMNS (
              "group" VARCHAR2(100) PATH '$.group',
              iid NUMBER(10) PATH '$.iid',
              branchId VARCHAR2(100) PATH '$.branchId',
              sicIid VARCHAR2(100) PATH '$.sicIid'
      ))) y

In Oracle 12.1.0.2 I get an error ORA-00936: missing expression Anybody has a clue how the correct syntax in that version has to be?

2

Answers


  1. Chosen as BEST ANSWER

    As p3consulting found out, in Oracle 12.1.0.2 it seems to be a problem to use a reserved word as a column name, even if you put it in quotes.


  2. NESTED PATH is supposed to work on 12.1, try replacing by grp without quotes

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search