By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH data (id, name, parent_id) as (
select 1, 'INDIA', 0 from dual union all
select 2, 'CANADA', 0 from dual union all
select 3, 'FINANCE', 1 from dual union all
select 4, 'IT', 2 from dual union all
select 5, 'HR', 3 from dual
),
rel_hier( id, name, parent_id, lvl ) AS (
SELECT id, name, parent_id, 1
FROM data
WHERE parent_id = 0
UNION ALL
SELECT n.id, n.name, n.parent_id,
lvl + 1
FROM rel_hier h
JOIN data n on n.parent_id = h.id
)
SEARCH DEPTH FIRST BY id SET rn
, rel_hier_with_leadlag AS (
SELECT r.*
, LAG(lvl) OVER(ORDER BY rn) AS lag_lvl
, LEAD(lvl,1) OVER(ORDER BY rn) AS llead_lvl -- we need to know which the latest node
, LEAD(lvl,1,1) OVER(ORDER BY rn) AS lead_lvl -- for the latest node we need to use 1 instead of NULL
, JSON_OBJECT(
'id' value id
, 'name' value name
ABSENT ON NULL
RETURNING CLOB
) js
FROM rel_hier r
)
SELECT
JSON_QUERY(
XMLCAST(
XMLAGG(
XMLELEMENT(e,
| {}js |
|---|
| [ { "id" : 1, "name" : "INDIA", "children" : [ { "id" : 3, "name" : "FINANCE", "children" : [ { "id" : 5, "name" : "HR" } ] } ] }, { "id" : 2, "name" : "CANADA", "children" : [ { "id" : 4, "name" : "IT" } ] } ] |