你可以创建一个递归函数：

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;
/

字符串
其中，对于样本数据：

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;

型
然后：

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

型
输出：
| JSON |
| ------------ |
| {"data":"[{"ID":1,"name":"INDIA","child":[{"ID":3,"name":"FINANCE","child":[{"ID":5,"name":"HR"}]}]},{"ID":2,"name":"CANADA","child":[{"ID":4,"name":"IT"}]}]"} |
fiddle

没有函数：你可以创建一个视图（最外面的JSON_QUERY只是为了漂亮的打印）
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
;

字符串