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?
4
Answers
You can use a recursive sub-query factoring clause:
Which, for the sample data:
Outputs:
fiddle
Shortest, still based on json_query ‘$..’ syntax: