Oracle 12.1.0.2中JSON嵌套信息的正确查询方法

以下SELECT语句可在Oracle 19c和12.2.0.1中正常执行，用于从JSON数据中提取信息，但在Oracle 12.1.0.2中运行时触发ORA-00936: missing expression错误，需要适配该版本的正确语法：

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

Oracle 12.1.0.2对JSON_TABLE的语法支持有限，不允许在顶层COLUMNS子句中直接嵌套NESTED PATH，需通过CROSS APPLY拆分嵌套数组的解析逻辑：

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 
  meta.validForClearingDay,
  entry."group",
  entry.iid,
  entry.branchId,
  entry.sicIid
from xmldata x
cross apply json_table(x.data, '$' 
  columns(
    validForClearingDay varchar2(100) path '$.metaData.validForClearingDay',
    entries_json varchar2(4000) format json path '$.entries'
  )
) meta
cross apply json_table(meta.entries_json, '$[*]'
  columns(
    "group" varchar2(100) path '$.group',
    iid number(10) path '$.iid',
    branchId varchar2(100) path '$.branchId',
    sicIid varchar2(100) path '$.sicIid'
  )
) entry;

关键修改说明 #

1. 先通过第一个JSON_TABLE提取顶层的validForClearingDay，同时将entries数组以JSON格式提取为临时字段entries_json
2. 再通过CROSS APPLY调用第二个JSON_TABLE，解析entries_json数组中的每个元素
3. 最终将顶层字段和嵌套数组字段关联输出，结果与高版本原语句一致

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