Oracle SQL get hierarchy of all children

Question

I have a table containing elements referencing their parent.

Nodes

ID	PARENT_ID
2002	2009
2003	2009
2007	2010
2008	2010
2009	2010
2010	NULL

To get the immediate children I use

SELECT n.ID as ID, n.PARENT_ID as PARENT_ID,
    (
        SELECT listagg(pn.ID,',') WITHIN GROUP (ORDER BY id) as CHILD_IDS 
        FROM NODES pn 
        WHERE pn.PARENT_ID = n.ID
    ) as CHILD_IDS  
FROM NODES n  

RESULT

ID	PARENT_ID	CHILD_IDs
2002	2009	NULL
2003	2009	NULL
2007	2010	NULL
2008	2010	NULL
2009	2010	2002,2003
2010	NULL	2007,2008,2009

But I need the hierarchical structure of children, ideally some structure similar to a JSON object. E.g. for the node 2010 the ideal result should be

[
  {
    2007: []
  },
  {
    2008: []
  },
  {
    2009: [
      {
        2002: []
      },
      {
        2003: []
      },
    ]
  }
]

How can I create a structure similar to this? I don't really know where to start.

Answer 1

That's hierarchical query; if you want to see it indented, then here's one option:

Sample data:

SQL> with test (id, parent_id) as
  2  (select
  3   2002, 2009 from dual union all select
  4   2003, 2009 from dual union all select
  5   2007, 2010 from dual union all select
  6   2008, 2010 from dual union all select
  7   2009, 2010 from dual union all select
  8   2010, null from dual
  9  )

Query:

 10  select lpad(' ', 2 * level - 1) || id as id
 11  from test
 12  start with parent_id is null
 13  connect by prior id = parent_id;

ID
--------------------------------------------------------------------------------
 2010
   2007
   2008
   2009
     2002
     2003

6 rows selected.

SQL>