Question 199556 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search