Hello can you please help me creating the json file for parent child relation.
Version : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
{code}
-- Test Data
CREATE TABLE RELATIONSHIP (Lvl NUMBER , NAME VARCHAR2(20), CHILD_ID NUMBER , PARENT_ID NUMBER, RN NUMBER);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, rn)
Values (1, 'A', 1, 1);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, parent_id, rn)
Values (2, 'B', 154, 1, 2);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, parent_id, rn)
Values (3, 'C', 122, 154, 3);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, parent_id, rn)
Values (3, 'D', 148, 154, 4);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, parent_id, rn)
Values (3, 'E', 150, 154, 5);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, parent_id, rn)
Values (2, 'F', 306, 1, 6);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, parent_id, rn)
Values (3, 'G', 311, 306, 7);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, parent_id, rn)
Values (3, 'H', 312, 306, 8);
Insert into CTSUCOREDR.RELATIONSHIP (lvl, name, child_id, parent_id, rn)
Values (2, 'I', 590, 1, 9);
COMMIT;
{code}
-- data output needed :
[
{
"Name": "A",
"Child_Id": 1,
"level2_nodes": [
{
"Name": "B",
"Child_Id": 154,
"level3_nodes": [
{
"Name": "C",
"Child_Id": 122
},
{
"Name": "D",
"Child_Id": 148
},
{
"Name": "E",
"Child_Id": 150
}
]
},
{
"Name": "F",
"Child_Id": 306,
"level3_nodes": [
{
"Name": "G",
"Child_Id": 311
},
{
"Name": "H",
"Child_Id": 312
}
]
},
{
"Name": "I",
"Child_Id": 590
}
]
}
]
Query i have to achieve current output.
{Code}
SELECT JSON_ARRAYAGG (
JSON_OBJECT (
'Name' VALUE name,
'Child_Id' VALUE t1.Child_Id,
'level2_nodes' VALUE
(SELECT JSON_ARRAYAGG (
JSON_OBJECT (
'Name' VALUE Name,
'Child_Id' VALUE t2.Child_Id,
'level3_nodes' VALUE
(SELECT JSON_ARRAYAGG (
JSON_OBJECT (
'Name' VALUE Name,
'Child_Id' VALUE t3.Child_Id,
'level4_nodes' VALUE
(SELECT JSON_ARRAYAGG (
JSON_OBJECT (
'Name' VALUE Name,
'Child_Id' VALUE t4.Child_Id
ABSENT ON NULL RETURNING CLOB)
ORDER BY t4.rn
ABSENT ON NULL RETURNING CLOB)
FROM RELATIONSHIP t4
WHERE t4.parent_id = t3.Child_id)
ABSENT ON NULL RETURNING CLOB)
ORDER BY t3.rn
ABSENT ON NULL RETURNING CLOB)
FROM RELATIONSHIP t3
WHERE t3.parent_id = t2.Child_id)
ABSENT ON NULL RETURNING CLOB)
ORDER BY t2.rn
ABSENT ON NULL RETURNING CLOB)
FROM RELATIONSHIP t2
WHERE t2.parent_id = t1.Child_id)
ABSENT ON NULL RETURNING CLOB)
ORDER BY t1.rn
ABSENT ON NULL RETURNING CLOB) json_str
FROM RELATIONSHIP t1
WHERE t1.parent_id IS NULL
ORDER BY t1.rn
{Code}