Forum Stats

  • 3,875,396 Users
  • 2,266,910 Discussions
  • 7,912,192 Comments

Discussions

How to prepare Hierarchical Tree (Table) to JSON for D3-Charts

Jegor Wieler
Jegor Wieler Member Posts: 9 Green Ribbon

Hello everyone,

I am just diving into plugins, which is working very well so far. Thanks to all the users who have helped me here at the forum.

My previous step was to use a JSON format as the data source in the plugin (link). This worked very well.

Now my next planned step is to use a hierarchical tree table as a region source to visualize the D3-Chart.

Basically, I want to write into the region source from the following table as a query and convert the data to a JSON format via the plugin's AJAX callback.

PARENT_ID	        CHILD_ID	        NAME				level
-			1			Root				0
1			2			First Top			1
1			4			Second Top			1
2			8			First Child			2
2			9			Second Child		        2	
4			11			First Child			2
4			12			Second Child		        2
4			13			Third Child			2
12			19			value 1				3
13			14			value 1				3
13			15			value 2				3
14			17			value 1				4
17			16			value 1				5
17			18			value 2				5

The entries should be linked via Child_IDs and Parent_IDs.

I have already tried some approaches that are very close (link). Unfortunately, the approaches I researched use packages and types which not every user has installed.

Is there a way to convert the table with AJAX-Callback to JSON format so that the hierarchy structure is preserved?

I would be very grateful for any advice!


Oracle Apex Version: 22.2

Best Regards

Jegor

Tagged:

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,254 Gold Crown

    At first appearance, a hierarchical query ( connect by ) looks like that might do the job. Its one of the few documented query styles whose results fit a pre-determined order.

    however, I'm concerned that children at the same level (eg CHILD_ID = 2,4 ) might be out of order.

    I'm aware that there is an "order children by" clause. but I'm not sure that it would do the task required. If the order of "first top", "second top" matter, you might want to add a column to the table to sort them efficiently.

    PS - A CREATE TABLE and INSERT statement would allow us to check/verify some ideas.

  • Paulzip
    Paulzip Member Posts: 8,808 Blue Diamond

    I wrote about a SQL only solution here

    You shouldn't store "level", it should be derived. Anyway, here's a solution:

    with 
      relationship(parent_id, child_id, name) as (
        select to_number(null), 1 , 'Root'         from dual union all
        select 1  ,             2 , 'First Top'    from dual union all
        select 1  ,             4 , 'Second Top'   from dual union all
        select 2  ,             8 , 'First Child'  from dual union all
        select 2  ,             9 , 'Second Child' from dual union all
        select 4  ,             11, 'First Child'  from dual union all
        select 4  ,             12, 'Second Child' from dual union all
        select 4  ,             13, 'Third Child'  from dual union all
        select 12  ,            19, 'value 1'      from dual union all
        select 13  ,            14, 'value 1'      from dual union all
        select 13  ,            15, 'value 2'      from dual union all
        select 14  ,            17, 'value 1'      from dual union all
        select 17  ,            16, 'value 1'      from dual union all
        select 17  ,            18, 'value 2'      from dual
      )
    , rel_hier(child_id, parent_id, name, lvl) as (
        select child_id, parent_id, name, 1             -- anchor
        from relationship
        where parent_id is null
        union all
        select n.child_id, n.parent_id, n.name, h.lvl + 1 -- recursion
        from rel_hier h
        join relationship n on n.parent_id = h.child_id
    )
    search depth first by child_id set rn         -- depth first traversal order given by rn, siblings ordered by name
    , rel_hier_with_leadlag as (
      select r.*
           , lag(lvl) over (order by rn) as lag_lvl         -- The previous level in recursive traversal
           , lead(lvl, 1, 1) over (order by rn) as lead_lvl -- The next level in recursive traversal, defaulted to 1 rather than null, as makes resolving closing tags easier
           , json_object(
               'child_id' value child_id
             , 'name'     value name
             ) jso
      from rel_hier r
    )
    select
      json_query(   -- This line not required
        xmlcast(    -- Concatenate lines together, working around listagg 4000 byte limit
          xmlagg(
            xmlelement(e,
              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
              end ||
              substr(jso, 1, length(jso) - 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
            )
            order by rn
          )
          as clob
        )
        , '$' returning clob pretty) json_res
    from rel_hier_with_leadlag
    /
    
    JSON_RES
    ---------------------------------
    {
      "child_id" : 1,
      "name" : "Root",
      "children" :
      [
        {
          "child_id" : 2,
          "name" : "First Top",
          "children" :
          [
            {
              "child_id" : 8,
              "name" : "First Child"
            },
            {
              "child_id" : 9,
              "name" : "Second Child"
            }
          ]
        },
        {
          "child_id" : 4,
          "name" : "Second Top",
          "children" :
          [
            {
              "child_id" : 11,
              "name" : "First Child"
            },
            {
              "child_id" : 12,
              "name" : "Second Child",
              "children" :
              [
                {
                  "child_id" : 19,
                  "name" : "value 1"
                }
              ]
            },
            {
              "child_id" : 13,
              "name" : "Third Child",
              "children" :
              [
                {
                  "child_id" : 14,
                  "name" : "value 1",
                  "children" :
                  [
                    {
                      "child_id" : 17,
                      "name" : "value 1",
                      "children" :
                      [
                        {
                          "child_id" : 16,
                          "name" : "value 1"
                        },
                        {
                          "child_id" : 18,
                          "name" : "value 2"
                        }
                      ]
                    }
                  ]
                },
                {
                  "child_id" : 15,
                  "name" : "value 2"
                }
              ]
            }
          ]
        }
      ]
    }
    
  • mathguy
    mathguy Member Posts: 10,912 Black Diamond

    Here is a much longer discussion of the same, with plain SQL and PL/SQL solutions at different levels of expertise and efficiency. If I recall correctly, it incorporates Paulzip's earlier work (which he linked to above).

    https://community.oracle.com/tech/developers/discussion/4494033/recursive-pl-sql-generation-of-hierarchical-json/p1

  • Jegor Wieler
    Jegor Wieler Member Posts: 9 Green Ribbon

    Hi everyone,

    thanks for your numerous tips. Through your help I understood the process from SQL/PLSQL to JSON.

    Unfortunately it is not what I am looking for, but its very clsoe.

    Basically, I want that is easy to use this PlugIn. The user should write the following query based on a table or with "dual" into the PlugIn region source.

    Input Region Source based on Paulzip`s code:

        select to_number(null), 1 , 'Root'         from dual union all
        select 1  ,             2 , 'First Top'    from dual union all
        select 1  ,             4 , 'Second Top'   from dual union all
        select 2  ,             8 , 'First Child'  from dual union all
        select 2  ,             9 , 'Second Child' from dual union all
        select 4  ,             11, 'First Child'  from dual union all
        select 4  ,             12, 'Second Child' from dual union all
        select 4  ,             13, 'Third Child'  from dual union all
        select 12  ,            19, 'value 1'      from dual union all
        select 13  ,            14, 'value 1'      from dual union all
        select 13  ,            15, 'value 2'      from dual union all
        select 14  ,            17, 'value 1'      from dual union all
        select 17  ,            16, 'value 1'      from dual union all
        select 17  ,            18, 'value 2'      from dual
    

    Or

    Select * from TEST_HIERACHY_TABLE
    

    The convert of the SQL/PLSQL to JSON should be done in my Plugin itself, in the AJAX-Callback.

    PlugIn AJAX-Callback based on Paulzip`s code:

    FUNCTION ajax_region(p_region IN apex_plugin.t_region,
                         p_plugin IN apex_plugin.t_plugin)
      RETURN apex_plugin.t_region_ajax_result IS
      --Plugin Attribute
      l_result apex_plugin.t_region_ajax_result;
      l_plsql  clob;
      
    BEGIN
        --Ausführen PL/SQL
        --apex_plugin_util.execute_plsql_code(p_plsql_code => l_plsql);
        l_plsql := p_region.source; 
    
        With rel_hier(child_id, parent_id, name, lvl) as (
            select child_id, parent_id, name, 1             -- anchor
            from (l_plsql) --Table isnt known yet
            where parent_id is null
            union all
            select n.child_id, n.parent_id, n.name, h.lvl + 1 -- recursion
            from rel_hier h
            join l_plsql n on n.parent_id = h.child_id --Table isnt known yet
        )search depth first by child_id set rn         -- depth first traversal order given by rn, siblings ordered by name
    
        ,rel_hier_with_leadlag as (
            select r.*
                   , lag(lvl) over (order by rn) as lag_lvl         -- The previous level in recursive traversal
                   , lead(lvl, 1, 1) over (order by rn) as lead_lvl -- The next level in recursive traversal, defaulted to 1 rather than null, as makes resolving closing tags easier
                   , json_object(
                       'child_id' value child_id
                     , 'name'     value name
                     ) jso
            from rel_hier r
            )
        select
          json_query(   -- This line not required
            xmlcast(    -- Concatenate lines together, working around listagg 4000 byte limit
              xmlagg(
                xmlelement(e,
                  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
                  end ||
                  substr(jso, 1, length(jso) - 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
                )
                order by rn
              )
              as clob
            )
            , '$' returning clob pretty) json_res
        from rel_hier_with_leadlag;
    
    RETURN l_result; 
    END ajax_region;
    

    There I cannot use a WITH clause based on the query from the PlugIn Region Source.

    Can my idea be realized?

    Also i thought about passing the query as a string to the plugin and execute it with "execute immediately ... using :PAGEITEM", but my problem there was that the number of PageItems are not given dynamically and must be predefined.

    Hopefully this explains my problem better than the first post.


    Thank you for your help.

    Best Regards

    Jegor


    PS:

    Create Table statement

    CREATE TABLE  "TEST_HIERACHY_TABLE" 
       (	"PARENT_ID" NUMBER, 
    	"CHILD_ID" NUMBER, 
    	"NAME" VARCHAR2(100)
       )
    

    Insert statement

    INSERT ALL 
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    VALUES (to_number(null), 1 , 'Root')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (1,2, 'First Top')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (1,4 , 'Second Top')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (2,8 , 'First Child')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (2,9 , 'Second Child')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (4,11, 'First Child')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (4,12, 'Second Child')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (4,13, 'Third Child')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (12,19, 'value 1')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (13,14, 'value 1')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (13,15, 'value 2')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (14,17, 'value 1')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (17,16, 'value 1')
    INTO TEST_HIERACHY_TABLE(parent_id, child_id, name)
    Values (17,18, 'value 2')
    Select * From Dual;