as_json: Relational to JSON in Oracle Database

Anton Scheffer December 22, 2015 11
0 0
Read Time:3 Minute, 58 Second

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 Post Author

Anton Scheffer

Oracle Consultant at AMIS
anton.scheffer@amis.nl
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Related posts:

Default ThumbnailCreating JSON document straight from SQL query – using LISTAGG and With Clause Absolutely Typical – The whole story on Types and how they power PL/SQL Interoperability (UKOUG, 2011) AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december OOW13: summarizing one week and 2000 sessions in 3 hours and a bit – the yearly AMIS OOW Review session – 10th October Default ThumbnailUsing an aggregation function to query a JSON-string straight from SQL

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

11 thoughts on “as_json: Relational to JSON in Oracle Database”

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

  4. 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

    1. And then I noticed the leading space… I guess the clue was in the line break added in the post above. However as_json still has the same problem, even when the leading space is removed for +ve decimals < 1
      Thanks

  5. 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

      1. Thank you for the hint to the documentation.
        I wasn’t aware of the xmltype constructor recieving a refcursor before.
        And of course, I am looking forward to the new version of as_json. Where will we find it ?

Comments are closed.

Next Post

Basic integration of Process Cloud Service with Document Cloud Service

Sun Dec 27 , 2015
Recently, Oracle had released a new version of Process Cloud Service. It mainly contains some minor improvements, but also has one major update: Oracle Process Cloud Service can now use Oracle Document Cloud Service for working with documents in business processes. This blog will show you how to make it […]

You May Like

%d bloggers like this: