Creating nested json data from Oracle SQL

Question

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?

Answer 1

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:

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