In a XML like this:

<item>
  <id>1</id>
  <name>ITEM 1</name>
  <subitems>
    <item>
      <id>2</id>
      <name>SUBITEM 1</name>
      <subitems/>
    </item>
    <item>
      <id>3</id>
      <name>SUBITEM 2</name>
      <subitems>
        <item>
          <id>4</id>
          <name>SUBITEM 3</name>
          <subitems/>
        </item>
      </subitems>
    </item>
  </subitems>
</item>

There can be several levels of subitems in the file. I can get all items (id and name) of parent and childs with this SQL:

SELECT * 
  FROM XMLTABLE ('//item' PASSING XMLTYPE (:XML) COLUMNS
                 ID VARCHAR2(5) PATH '/item/id',
                 NAME VARCHAR2(50) PATH '/item/name')

Result:

ID | NAME
------------------
1  | ITEM 1
2  | SUBITEM 1
3  | SUBITEM 2
4  | SUBITEM 3

How can I do it in the corresponding JSON below using JSON_TABLE?

{
  "id": 1,
  "name": "ITEM 1",
  "subitems": [
    {
      "id": 2,
      "name": "SUBITEM 1",
      "subitems": [
      ]
    },
    {
      "id": 3,
      "name": "SUBITEM 2",
      "subitems": [
        {
          "id": 4,
          "name": "SUBITEM 3",
          "subitems": [
          ]
        }
      ]
    }
  ]
}

In short, how to extract values from JSON without informing the full path?

CodePudding user response：

You can use a recursive sub-query factoring clause:

WITH rsqfc (id, name, subitems) AS (
  SELECT j.id,
         j.name,
         j.subitems
  FROM   table_name t
         CROSS APPLY JSON_TABLE(
           t.value,
           '$'
           COLUMNS (
             id       NUMBER       PATH '$.id',
             name     VARCHAR2(20) PATH '$.name',
             subitems JSON         PATH '$.subitems'
           )
         ) j
UNION ALL
  SELECT rj.id,
         rj.name,
         rj.subitems
  FROM   rsqfc r
         CROSS APPLY JSON_TABLE(
           r.subitems,
           '$[*]'
           COLUMNS (
             id       NUMBER       PATH '$.id',
             name     VARCHAR2(20) PATH '$.name',
             subitems JSON         PATH '$.subitems'
           )
         ) rj
)
CYCLE subitems SET is_cycle TO 1 DEFAULT 0
SELECT id, name
FROM   rsqfc

Which, for the sample data:

CREATE TABLE table_name (value JSON );

INSERT INTO table_name (value) VALUES ('{
  "id": 1,
  "name": "ITEM 1",
  "subitems": [
    {
      "id": 2,
      "name": "SUBITEM 1",
      "subitems": []
    },
    {
      "id": 3,
      "name": "SUBITEM 2",
      "subitems": [
        {
          "id": 4,
          "name": "SUBITEM 3",
          "subitems": []
        }
      ]
    }
  ]
}');

Outputs:

ID	NAME
1	ITEM 1
2	SUBITEM 1
3	SUBITEM 2
4	SUBITEM 3

fiddle

CodePudding user response：

with data(js) as (
    select 
    q'~{
      "id": 1,
      "name": "ITEM 1",
      "subitems": [
        {
          "id": 2,
          "name": "SUBITEM 1",
          "subitems": [
          ]
        },
        {
          "id": 3,
          "name": "SUBITEM 2",
          "subitems": [
            {
              "id": 4,
              "name": "SUBITEM 3",
              "subitems": [
              ]
            }
          ]
        }
      ]
    }~'
    from dual
),
tdata(ids, names) as (
    select 
        json_query( cast(js as varchar2(4000)), '$..id' returning varchar2 with wrapper),
        json_query( cast(js as varchar2(4000)), '$..name' returning varchar2 with wrapper)
    from data
)
select id, name from (
    select rn, id from tdata,
    json_table(ids,
        '$[*]'
        columns (
            rn for ordinality,
            id PATH '$'
        ) 
    )
) tids
join (
    select rn, name from tdata,
    json_table(names,
        '$[*]'
        columns (
            rn for ordinality,
            name PATH '$'
        ) 
    )
) tnames on tids.rn = tnames.rn
;

1   ITEM 1
2   SUBITEM 1
3   SUBITEM 2
4   SUBITEM 3

CodePudding user response：

Shortest, still based on json_query '$..' syntax:

with data(js) as (
    select 
    q'~{
      "id": 1,
      "name": "ITEM 1",
      "subitems": [
        {
          "id": 2,
          "name": "SUBITEM 1",
          "subitems": [
          ]
        },
        {
          "id": 3,
          "name": "SUBITEM 2",
          "subitems": [
            {
              "id": 4,
              "name": "SUBITEM 3",
              "subitems": [
              ]
            }
          ]
        }
      ]
    }~'
    from dual
),
tdata(js) as (
    select json_object(
        'ids' value json_query( cast(js as varchar2(4000)), '$..id' returning varchar2 with wrapper),
        'names' value json_query( cast(js as varchar2(4000)), '$..name' returning varchar2 with wrapper)
    )
    from data
)
select 
    max(id) as id, max(name) as name
from tdata, json_table(
    js,
    '$' 
    columns(
        nested '$.ids'
        columns (
            nested '$[*]'
            columns (
                id_idx for ordinality,
                id  number PATH '$'
            )
        ),
        
        nested '$.names'
        columns (
            nested '$[*]'
            columns (
                name_idx for ordinality,
                name  varchar2(64) PATH '$'
            )
        )
    )
)
group by coalesce(id_idx,name_idx) 
;

CodePudding user response：

select * from json_table(
q'~{
  "id": 1,
  "name": "ITEM 1",
  "subitems": [
    {
      "id": 2,
      "name": "SUBITEM 1",
      "subitems": [
      ]
    },
    {
      "id": 3,
      "name": "SUBITEM 2",
      "subitems": [
        {
          "id": 4,
          "name": "SUBITEM 3",
          "subitems": [
          ]
        }
      ]
    }
  ]
}~',
'$'
columns (
    id NUMBER(10,0) path '$.id',
    name VARCHAR2(64) path '$.name'
)
)
;