Relational to JSON With APEX_JSON

APEX_JSON was primarily intended to be used by APEX developers — but there are some hooks that can allow it to be used as a standalone PL/SQL package.

APEX_JSONis a PL/SQL API included with Oracle Application Express (APEX) 5.0 that provides utilities for parsing and generating JSON. While APEX_JSON was primarily intended to be used by APEX developers, there are some hooks that can allow it to be used as a standalone PL/SQL package.

Solution

The following solution uses APEX_JSON to create the JSON that represents a department in the HR schema. APEX_JSON basically writes JSON content to a buffer. By default, the buffer used is the HTP buffer in the database, as that's what APEX reads. But as you can see with line 27, it's possible to redirect the output to a CLOB buffer instead. Once we've redirected the output, we can make API calls to open/close objects and arrays and write values to them. When we're done writing out the JSON we can make a call to get_clob_output to get the JSON contents. I've highlighted some of the relevant lines...

create or replace function get_dept_apex_json(
 p_dept_id in departments.department_id%type
)

 return clob

is

 cursor manager_cur (
 p_manager_id in employees.employee_id%type
 )
 is
 select *
 from employees
 where employee_id = manager_cur.p_manager_id;

 l_date_format constant varchar2(20) := 'DD-MON-YYYY';
 l_dept_rec departments%rowtype;
 l_dept_json_clob clob;
 l_loc_rec locations%rowtype;
 l_country_rec countries%rowtype;
 l_manager_rec manager_cur%rowtype;
 l_job_rec jobs%rowtype;

begin

 apex_json.initialize_clob_output;

 select *
 into l_dept_rec
 from departments
 where department_id = get_dept_apex_json.p_dept_id;

 apex_json.open_object(); --department

 apex_json.write('id', l_dept_rec.department_id);
 apex_json.write('name', l_dept_rec.department_name);

 select *
 into l_loc_rec
 from locations
 where location_id = l_dept_rec.location_id;

 apex_json.open_object('location');

 apex_json.write('id', l_loc_rec.location_id);
 apex_json.write('streetAddress', l_loc_rec.street_address);
 apex_json.write('postalCode', l_loc_rec.postal_code);

 select *
 into l_country_rec
 from countries cou
 where cou.country_id = l_loc_rec.country_id;

 apex_json.open_object('country');

 apex_json.write('id', l_country_rec.country_id);
 apex_json.write('name', l_country_rec.country_name);
 apex_json.write('regionId', l_country_rec.region_id);

 apex_json.close_object(); --country

 apex_json.close_object(); --location

 open manager_cur(l_dept_rec.manager_id);
 fetch manager_cur into l_manager_rec;

 if manager_cur%found
 then
 apex_json.open_object('manager');

 apex_json.write('id', l_manager_rec.employee_id);
 apex_json.write('name', l_manager_rec.first_name || ' ' || l_manager_rec.last_name);
 apex_json.write('salary', l_manager_rec.salary);

 select *
 into l_job_rec
 from jobs job
 where job.job_id = l_manager_rec.job_id;

 apex_json.open_object('job');

 apex_json.write('id', l_job_rec.job_id);
 apex_json.write('title', l_job_rec.job_title);
 apex_json.write('minSalary', l_job_rec.min_salary);
 apex_json.write('maxSalary', l_job_rec.max_salary);

 apex_json.close_object(); --job

 apex_json.close_object(); --manager
 else
 apex_json.write('manager', '', p_write_null => true);
 end if;

 close manager_cur;

 apex_json.open_array('employees');

 for emp_rec in (
 select *
 from employees
 where department_id = l_dept_rec.department_id
 )
 loop
 apex_json.open_object(); --employee

 apex_json.write('id', emp_rec.employee_id);
 apex_json.write('name', emp_rec.first_name || ' ' || emp_rec.last_name);
 apex_json.write('isSenior', emp_rec.hire_date < to_date('01-jan-2005', 'dd-mon-yyyy'));
 apex_json.write('commissionPct', emp_rec.commission_pct, p_write_null => true);

 apex_json.open_array('jobHistory');

 for jh_rec in (
 select job_id,
 department_id,
 start_date,
 end_date
 from job_history
 where employee_id = emp_rec.employee_id
 )
 loop
 apex_json.open_object(); --job

 apex_json.write('id', jh_rec.job_id);
 apex_json.write('departmentId', jh_rec.department_id);
 apex_json.write('startDate', to_char(jh_rec.start_date, l_date_format));
 apex_json.write('endDate', to_char(jh_rec.end_date, l_date_format));

 apex_json.close_object(); --job
 end loop;

 apex_json.close_array(); --jobHistory

 apex_json.close_object(); --employee
 end loop;

 apex_json.close_array(); --employees
 apex_json.close_object(); --department

 l_dept_json_clob := apex_json.get_clob_output;

 apex_json.free_output;

 return l_dept_json_clob;

exception

 when others
 then 
 if manager_cur%isopen
 then
 close manager_cur;
 end if;

 raise;

end get_dept_apex_json;

Output

When passed a departmentId of 10 , the function returns a CLOB populated with JSON that matches the goal 100%.

{
 "id": 10,
 "name": "Administration",
 "location": {
 "id": 1700,
 "streetAddress": "2004 Charade Rd",
 "postalCode": "98199",
 "country": {
 "id": "US",
 "name": "United States of America",
 "regionId": 2
 }
 },
 "manager": {
 "id": 200,
 "name": "Jennifer Whalen",
 "salary": 4400,
 "job": {
 "id": "AD_ASST",
 "title": "Administration Assistant",
 "minSalary": 3000,
 "maxSalary": 6000
 }
 },
 "employees": [
 {
 "id": 200,
 "name": "Jennifer Whalen",
 "isSenior": true,
 "commissionPct": null,
 "jobHistory": [
 {
 "id": "AD_ASST",
 "departmentId": 90,
 "startDate": "17-SEP-1995",
 "endDate": "17-JUN-2001"
 },
 {
 "id": "AC_ACCOUNT",
 "departmentId": 90,
 "startDate": "01-JUL-2002",
 "endDate": "31-DEC-2006"
 }
 ]
 }
 ]
}

Summary

I really enjoyed working with APEX_JSON — it's my new "go-to" for PL/SQL based JSON generation. APEX_JSON has a very light footprint (it's just a single package) and it takes a minimalistic approach. Rather than compose objects as one would do with PL/JSON, you simply use the package to write JSON to a buffer.

This approach yields some performance benefits, as well. In a basic test where I generated the JSON for every department in the HR schema 100 times in a loop, the APEX_JSON-based solution finished at around 3.5 seconds whereas the PL/JSON based solution took around 17 seconds. That means APEX_JSON about 3.8 times faster than PL/JSON when it comes generating JSON and converting it to a CLOB.

Unfortunately, APEX_JSON is only included with APEX 5.0+. Upgrading your database's APEX instance seems a little extreme if all you want to do is work with JSON (though it is free and doesn't take too long), but if you already have APEX 5.0, then it's a very nice tool to be able to leverage.