Creating nested json data from Oracle SQL

I am trying to create a nested json object using Oracle SQL. I am able to create JSON objects where the hierarchy level is predefined. In this case, it is dynamic and not able to find SQL or PLSQL solution for this nested data.

I have a table with below data,

Table data

I want the output as below using Oracle SQL or PLSQL

{
  "data": [
    {
      "ID": 1,
      "NAME": "India",
      "child": [
        {
          "ID": 3,
          "NAME": "FINANCE",
          "child": [
            {
              "ID": 5,
              "NAME": "HR"
            }
          ]
        }
      ]
    },
    {
      "ID": 2,
      "NAME": "Canada",
      "child": [
        {
          "ID": 4,
          "NAME": "IT"
        }
      ]
    }
  ]
}

Can anyone help me on this?

You can create a recursive function:

CREATE FUNCTION generate_json(
  i_parent_id IN NUMBER
) RETURN CLOB
IS
  v_json CLOB;
BEGIN
  SELECT JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'ID' VALUE id,
             KEY 'name' VALUE name,
             KEY 'child' VALUE generate_json(id) FORMAT JSON ABSENT ON NULL
           )
         )
  INTO   v_json
  FROM   table_name
  WHERE  parent_id = i_parent_id;

  RETURN v_json;
END;
/

Which, for the sample data:

CREATE TABLE table_name(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;

Then:

SELECT JSON_OBJECT(KEY 'data' VALUE generate_json(0)) AS json FROM DUAL;

Outputs:

fiddle

Without a function: you can make a view of it (the outermost JSON_QUERY is just there for the pretty printing)

https://dbfiddle.uk/XgZ_IluN

    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,
              CASE WHEN rn = 1 THEN '[' END ||
              CASE
                WHEN lvl - lag_lvl = 1 THEN ',"children":['    -- Level incremented by one, so child level, start array
                WHEN lvl > 1 then ','                          -- appending when not first level
                WHEN rn>1 AND parent_id = 0 THEN ','      -- appending when a root node but not the first one
              END ||
              SUBSTR(js, 1, LENGTH(js) - 1) ||                  -- remove last brace, as we are controlling children
              CASE
                WHEN lvl >= lead_lvl then '}' ||               -- Level same or greater than next level, so close json_object
                     RPAD(' ', (lvl - lead_lvl) * 2 + 1, ']}') -- and add as many closing array / object blocks as required
              END ||
              CASE WHEN llead_lvl IS NULL THEN ']' END          -- when the latest node 
            ) 
            ORDER BY rn
          )
        AS CLOB
      )
      , '$' RETURNING CLOB PRETTY
    ) as js

FROM rel_hier_with_leadlag r
;