By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
5 rows affected
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;
/
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"}]}]"} |