问嵌套JSON的Oracle查询(具有重复字段名和动态多嵌套级别)

EN

CREATE TABLE t (
  value CLOB
  CONSTRAINT ensure_json CHECK( value IS JSON )
);

INSERT INTO t VALUES ( '{"id":1000,"version":1,"number":"123456","type":"Y","itemName":"some name1","listOfItems":[{"id":2000,"version":2,"number":"234567","type":"Y","itemName":"some name2","listOfItems":[{"id":3000,"version":3,"number":"345678","type":"Y","itemName":"some name3","listOfItems":[{"id":4000,"version":4,"number":"456789","type":"N","itemName":"some name2","listOfItems":[{"id":5000,"version":5,"number":"567890","type":"N","itemName":"some name3","listOfItems":[]},{"id":6000,"version":6,"number":"678901","type":"N","itemName":"some name3","listOfItems":[]}]}]}]}]}' );

WITH data ( parent, id, version, "number", "type", itemName, child ) AS (
  SELECT DISTINCT
         NULL,
         j.id,
         j.version,
         j."number",
         j."type",
         j.itemName,
         j.child
  FROM   t,
         JSON_TABLE(
           t.value,
           '$'
           COLUMNS (
             id       NUMBER       PATH '$.id',
             version  VARCHAR2(20) PATH '$.version',
             "number" VARCHAR2(20) PATH '$.number',
             "type"   VARCHAR2(20) PATH '$.type',
             itemName VARCHAR2(20) PATH '$.itemName',
             child    VARCHAR2(4000) FORMAT JSON PATH '$.listOfItems'
           )
         ) j
UNION ALL
  SELECT d.id,
         k.id,
         k.version,
         k."number",
         k."type",
         k.itemName,
         k.child
  FROM   data d,
         JSON_TABLE(
           d.child,
           '$'
           COLUMNS (
             NESTED PATH '$[*]'
               COLUMNS (
                 id       NUMBER       PATH '$.id',
                 version  VARCHAR2(20) PATH '$.version',
                 "number" VARCHAR2(20) PATH '$.number',
                 "type"   VARCHAR2(20) PATH '$.type',
                 itemName VARCHAR2(20) PATH '$.itemName',
                 child    VARCHAR2(4000) FORMAT JSON PATH '$.listOfItems'
               )
           )
         ) k
  WHERE d.child IS NOT NULL
  AND   d.child <> '[]'
)
SELECT parent,
       id,
       version,
       "number",
       "type",
       itemName
FROM   data;

PARENT   ID VERSION number type ITEMNAME
------ ---- ------- ------ ---- ----------
(NULL) 1000       1 123456    Y some name1
  1000 2000       2 234567    Y some name2
  2000 3000       3 345678    Y some name3
  3000 4000       4 456789    N some name2
  4000 5000       5 567890    N some name3
  4000 6000       6 678901    N some name3