Dynamic json_list of json_list of json_list

Ja Steam
2013-09-28
2013-10-13
  • Ja Steam

    Ja Steam - 2013-09-28

    Hello all,

    I've been trying to build a json list of list of list for several days. Yes, the frontend UI (tree grid) only accepts an array of arrays.

    I know this is a little unusual, but basically, I'm trying to get arrays in array so that I can add json objects.

    At the top level I'm using executeList wich returns two objects.

    Level 1 (print of the json_list - Level 1)

    [{
      "id" : 70070037001,
      "text" : "List 1",
      "isleaf" : 0
    }, {
      "id" : 70070037002,
      "text" : "List 2 ",
      "isleaf" : 0
    }]
    

    At the second level, I need to add json object to each of the level 1, there si where I start going wrong:

    Level 2

    -- now lets try to add this object to the FIRST 
    
    temp := json(list1.get(1));       --> this does not work
                                    --> this will go into a loop, but need to get each element in array
    
    temp.put('children', list2.to_json_value);
    

    This is where I get completely lost.

    Level 3

    -- now lets try to add this object to the children to the new list of elements
    
    temp := json(list2.get('children'));       --> this does not work
                                    --> this will go into a loop, but need to get each element in array
    
    temp.put('children', list3.to_json_value);
    

    Here is what I would need to produce dynamically (built by executeList)

    [{
        "id": 70070037001,
        "text": "List 1",
        "isleaf": 0,
        "children": [
            {
                "oid": 100,
                "text": "Innerlistobject100",
                "isleaf": 0,
                "children": [
                    {
                        "sid": 1000,
                        "text": "Innerlistobject1000",
                        "isleaf": 1
                    },
                    {
                        "sid": 2000,
                        "text": "Innerlistobject2000",
                        "isleaf": 1
                    }
                ]
            },
            {
                "oid": 200,
                "text": "Innerlistobject200",
                "isleaf": 0,
                "children": [
                    {
                        "sid": 1000,
                        "text": "Innerlistobject1000",
                        "isleaf": 1
                    },
                    {
                        "sid": 2000,
                        "text": "Innerlistobject2000",
                        "isleaf": 1
                    }
                ]
            }
        ]
    }]
    
     
  • Ja Steam

    Ja Steam - 2013-09-29

    Hello All,

    You can ignore this. After working with the examples, I managed to get this to work.
    For those who might be interested in knowing, here is the solution:

    ~~~~~~~~~
    set serveroutput on
    DECLARE
    --Input/Output Variables
    returnlist json_list;

    --JSON Manipulation Variables
    obj json;
    ret1 json_list;
    ret2 json_list;
    ret3 json_list;
    temp json;
    tempobj1 json;
    tempobj2 json;
    tempobj3 json;
    temparray json_list;
    tempinner json_list;
    templist json_list;
    tempdata json_value;
    i number := 0;
    j number := 1;

    --Error Handling
    errCode varchar2(100);
    errMsg varchar2(250);

    BEGIN

    obj := json();
    temparray := json_list();
    tempinner := json_list();
    templist := json_list();
    returnlist := json_list();

    ret1 := json_dyn.executeList('SELECT col1 , col2
    FROM my_tbl3
    ORDER BY 1');

    WHILE i <= 1..ret1.count LOOP

    ret2 := json_dyn.executeList('SELECT col1 , col2
                                FROM my_tbl3
                                ORDER BY 1'); 
    tempdata := ret1.get(i);
    tempobj1 := json(tempdata);
    tempobj1.put('sub_categories', ret2.to_json_value);
    temparray.append(tempobj1.to_json_value);
    
    tempobj2 := json(temparray.get(i));
    tempinner := json_ext.get_json_list(tempobj2,'sub_categories');
    
    WHILE j <= 1..tempinner.count LOOP
      tempdata := tempinner.get(j);
      temp := json(tempdata);
    
      ret3 := json_dyn.executeList('SELECT col1 , col2
                                FROM my_tbl3
                                ORDER BY 1');
    
      temp.put('sub_categories', ret3.to_json_value);
      templist.append(temp.to_json_value);
    j := j + 1;
    END LOOP;
    
    obj := json(temparray.get(i));
    obj.put('children', templist);
    returnlist.append(obj.to_json_value);
    

    i := i +1;
    END LOOP;

    --returnlist.print;
    returnlist.htp();

    EXCEPTION
    WHEN OTHERS
    THEN
    errCode := SQLCODE;
    errMsg := REPLACE(SUBSTR(SQLERRM, 1 , 200),'"','' );

    HTP.P('{"success": false, "errCode":"'||errCode||'", "errMsg":"'||errMsg||'"}');
    

    END;

    ~~~~~~~~~~~~~~

    Cheers

    Jan S.

     
    Last edit: Ja Steam 2013-10-13
  • Ja Steam

    Ja Steam - 2013-10-13

    Small corection in above script. Need to use a WHILE loop to evaluete the iterator value before entering the loop otherwise you end up with an additional json list of arrays.

    jan S.

     

Log in to post a comment.