4 Replies Latest reply on Jan 6, 2016 8:01 AM by ascheffer

    Using APEX_JSON and extending output

    Scott Wesley

      The code below

      DECLARE
        c sys_refcursor;
      BEGIN
        open c for
          select empno    as id
             ,ename as content
          from  emp
          where rownum <= 2
        ;
        apex_json.open_array;
        apex_json.write(c); -- just this can be enough
      
      
        apex_json.write('ID',0);
        apex_json.write('CONTENT','Example');
        apex_json.close_array;
      END;
      

       

      Produces the following output.

      [ { "ID":7839 ,"CONTENT":"KING" } ,{ "ID":7698 ,"CONTENT":"BLAKE" } ] ,"ID":0 ,"CONTENT":"Example" ]
      


      If I use just the marked line 11 to write the cursor output I get.

      [ { "ID":7839 ,"CONTENT":"KING" } ,{ "ID":7698 ,"CONTENT":"BLAKE" } ]
      

       

      I would like it to produce content as if the second component was part of the query.

       

      [ { "ID":7839 ,"CONTENT":"KING" } ,{ "ID":7698 ,"CONTENT":"BLAKE" }, {"ID":0 ,"CONTENT":"Example" } ] 

       


      I realise in this case I could simply add a union to the query, but I was trying to test the flexibility of the tool. The chart I'm using (vis.js) accepts rather heterogeneous JSON content.

      DECLARE
        c sys_refcursor;
      BEGIN
        open c for
          select empno    as id
             ,ename as content
          from  emp
          where rownum <= 2
          union all
          select 0 as id
                ,'Example' as content
          from dual
        ;
        apex_json.write(c);
      END;
      


      I haven't used apex_json too much, but it's like there needs to be a parameter to suppress surrounding [ ] brackets. Well, not need, but it'd be nice.

       

      APEX 5.0.2