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
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.
NESTED PATH is supposed to work on 12.1, try replacing by grp without quotes