as_json: Relational to JSON in Oracle Database


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 database

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


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.

  function get_dept_as_json( p_dept_id number )
  return clob
    cursor c_dept
      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;
    open c_dept;
    fetch c_dept into r_dept;
    close c_dept;
    dbms_lob.createtemporary( l_rv, true, );
    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
      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
        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( )
                              , '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 );;
    return l_rv;
  dbms_output.put_line( get_dept_as_json( 10 ) );


Used source
The package

About Author

Oracle Consultant at AMIS


  1. 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,

  2. 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 ?


Leave a Reply

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