as_json: Relational to JSON in Oracle Database

11

Some time ago I noticed this blog from Dan McGhan. In that blog he compares several ways to generate JSON from relational data in a Oracle Database.
I had some spare time, so I tried my own JSON generator, build around 3 nested Oracle types, on the examples he used.
I had no problem generating the exact output he wanted, but the performance was a bit disappointing (almost the same as the PL/JSON solution).
But, as I had some spare time, I started trying to improve the performance. But after a few tries it turned out that I was lucky to get a result at all.
My JSON generator had a gigantic memory leak. I not sure if it’s because of Oracle’s implementation of the nested types I used

create or replace type jd authid current_user as object
( json_type varchar2(1 char)
) not final;
/

create or replace type ja as table of jd;
/

create or replace type jv authid current_user under jd
( .... )

but using this script is enough to crash my 12.1.02.0 database

declare
  x jv;
begin
  for i in 1 .. 12 
  loop
    x := jv( 'a', jv( 'b', jv( 'x' ) )
           , 'a', jv( 'b', jv( 'x' ) )
           );
  end loop;
end;
/

Crashed

Anyway, I had some spare time and turned my JSON generator in a one package implementation. And using that package I can create the same JSON as Dan is creating with PL/JSON and APEX_JSON.
But with performance a bit better than the APEX_JSON solution.

declare
-- 
  function get_dept_as_json( p_dept_id number )
  return clob
  is
    cursor c_dept
    is
      select d.department_id
           , d.department_name
           , l.location_id
           , l.street_address
           , l.postal_code
           , c.country_id
           , c.country_name
           , c.region_id
           , m.employee_id
           , m.first_name || ' ' || m.last_name manager_name
           , m.salary
           , j.job_id
           , j.job_title
           , j.min_salary
           , j.max_salary
      from departments d
         , locations l
         , countries c
         , employees m
         , jobs j
      where d.department_id = p_dept_id
      and   l.location_id = d.location_id
      and   c.country_id = l.country_id
      and   m.employee_id (+) = d.manager_id
      and   j.job_id (+) = m.job_id;
    r_dept c_dept%rowtype;
    l_jv as_json.tp_json_value;
    l_emps as_json.tp_json_value;
    l_hist as_json.tp_json_value;
    l_date_format varchar2(20) := 'DD-MON-YYYY';
    l_rv clob;
  begin
    open c_dept;
    fetch c_dept into r_dept;
    close c_dept;
--
    dbms_lob.createtemporary( l_rv, true, dbms_lob.call );
    l_jv := as_json.json( 'id', as_json.jv( r_dept.department_id )
              , 'name', as_json.jv( r_dept.department_name )
              , 'location', as_json.json( 'id', as_json.jv( r_dept.location_id )
                              , 'streetAddress', as_json.jv( r_dept.street_address )
                              , 'postalCode', as_json.jv( r_dept.postal_code )
                              , 'country', as_json.json( 'id', as_json.jv( r_dept.country_id )
                                             , 'name', as_json.jv( r_dept.country_name )
                                             , 'regionId', as_json.jv( r_dept.region_id )
                                             )
                              )
              , 'manager', as_json.json( 'id', as_json.jv( r_dept.employee_id )
                             , 'name', as_json.jv( r_dept.manager_name )
                             , 'salary', as_json.jv( r_dept.salary )
                             , 'job', as_json.json( 'id', as_json.jv( r_dept.job_id )
                                        , 'title', as_json.jv( r_dept.job_title )
                                        , 'minSalary', as_json.jv( r_dept.min_salary )
                                        , 'maxSalary', as_json.jv( r_dept.max_salary )
                                        )
                             )
              );
--
    for r_emp in ( select e.employee_id
                        , e.first_name || ' ' || e.last_name name
                        , e.hire_date
                        , e.commission_pct
                   from employees e
                   where e.department_id = r_dept.department_id
                 )
    loop
      l_hist := null;
      for r_hist in ( select h.job_id
                           , h.department_id
                           , h.start_date
                           , h.end_date
                      from job_history h
                      where h.employee_id = r_emp.employee_id
                    )
      loop
        l_hist := as_json.add_item( l_hist
                          , as_json.json( 'id', as_json.jv( r_hist.job_id )
                                , 'departmentId', as_json.jv( r_hist.department_id )
                                , 'startDate', as_json.jv( r_hist.start_date, l_date_format )
                                , 'endDate', as_json.jv( r_hist.end_date, l_date_format )
                                )
                          );
      end loop;
      l_emps := as_json.add_item( l_emps
                        , as_json.json( 'id', as_json.jv( r_emp.employee_id )
                              , 'name', as_json.jv( r_emp.name )
                              , 'isSenior', as_json.jv( r_emp.hire_date < to_date( '01-jan-2005', 'dd-mon-yyyy' ) )
                              , 'commissionPct', as_json.jv( r_emp.commission_pct )
                              , 'jobHistory', l_hist
                              )
                        );
    end loop;
    as_json.add_member( l_jv, 'employees', l_emps );
--
    l_rv := as_json.stringify( l_jv );
    as_json.free;
    return l_rv;
  end;
begin
  dbms_output.put_line( get_dept_as_json( 10 ) );
end;

Anton

Used source
The package

About Author

Oracle Consultant at AMIS

11 Comments

  1. Mathieu Meeuwissen on

    Hi Anton,

    Thanks for the package.
    For me it is very useful as the apex_json package is giving me some problems on a 12.1 database and the jsontable functionality is not yet available on 12.1 for creating json.

    However I ran into a small issue.
    When I use de json_ec function it raises a too many open cursors error at line 1521.
    This is happening because the rc ref cursor isn’t closed in the dynamic sql code.
    If you add ” close rc;” after the loop statement at line 1530, it is solved.

    regards,

    Mathieu

  2. Mathieu Meeuwissen on

    Hello Anton,

    Thanks very much for this package.
    It is very useful when you have a database version older than 12.2.
    As I ran into problems with the apex_json package

    There is a bug I ran into when I used the json_ec function:
    “ORA-01000: maximum open cursors exceeded at line 1524″.

    The code will execute some fetching of a ref cursor using execute immediate.
    But the ref cursor isn’t closed afterwards.
    So add the line ” close rc;” after the loop at line 1530 and it is solved.

    regards,

    Mathieu

  3. Steve Dowey on

    Hi Anton,
    thank you for the package. I’m finding it invaluable. However, when I have a number less than 1, that is already represented as JSON, such as the ‘Pitch’ value in the JSON below:

    {“Item #”:”T9070360″,”d1″:”#4″,”Pitch”: 0.66,”Limit”:”Z3″,”l1″:50,”l2″:17,”l3″:null,”d2″:3.55,”sq”:2.8,”l4″:5,”z”:3,”Drill Ø”:3}

    when I add it to a larger object as a member (using:
    as_json.add_member(l_jp,’items’, AS_JSON.json(l_ritem)); //the l_ritem contains the item metadata object above
    )
    , as part of a larger string, then the number loses it’s leading 0:
    “itemMetadata”:{“Item #”:”T9070360″,”d1″:”#4″,”Pitch”:.66,”Limit”:”Z3″,”l1″:50,”l2″:17,”l3″:null,”d2″:3.55,”sq”:2.8,”l4″:5,”z”:3,”Drill \u00D8″:3}

    I originally wrote a simple function to create the correct JSON number identifier and I had to modify my to_char to fix it on the original string. I hoped that would be the issue. However I can’t find the equivalent in the as_json package as I think it will be a similar problem?

    Thanks for your help,
    Regards,
    Steve

  4. Hi Anton

    Thank you for your package AS_JSON which offers valuable insights. I am struggling with a similar task since a couple of days, but my select statements to create the json data contain sub-cursors embedded with the cursor function, as in

    select d.*
    ,cursor (
    select e.*
    from emp e
    where e.deptno = d.deptno
    ) employee
    from dept d ;

    Such columns can be described by dbms_sql.describe_columns3, they yield a col_type of 102. I didn’t find a branch processing this very col_type in AS_JSON, but I must confess that I don’t know of any way manipulating columns with type cursor with dbms_sql, as well. According to Tom Kyte, there actually is no way (or was no way in 20123).

    On the other hand, DBMS_XMLQUERY, DBMS_XMLGEN and APEX_JSON somehow do the magic. DBMS_XMLQUERY maybee uses Java, but I am pretty sure DBMS_XMLGEN and APEX_JSON don’t. Can you imagine how this can be done ?

    Regards
    Pascal

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.