PL/JSON is an open source library for working with JSON in Oracle Database. Originally started in 2009, the project has undergone many updates and bug fixes over the years and is now quite stable and feature rich. PL/JSON was first hosted on SourceForge (and it’s still available there) but it’s probably best to get it from its new home on GitHub.
After downloading and extracting the contents of the project, installation is as simple as running the install.sql script from a client like SQL Plus or SQL Developer. The installation scripts install a total of 9 packages and 4 object types. The core of PL/JSON is based on 2 object types, JSON and JSON_LIST, which correspond to the two types of structures that exist in JSON: objects and arrays.
Solution
The following solution uses PL/JSON to create the JSON that represents a department in the HR schema. Because PL/JSON makes use of some of the object-oriented features of Oracle Database, the solution is somewhat object-oriented as well. Smaller data structures are created and used to compose a larger JSON object which represents our department. Finally, a call to the to_clob method of the JSON object serializes the JSON so that we can move it around. I’ve highlighted some of the relevant lines…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 |
create or replace function get_dept_pljson( 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_dept_json_obj json; l_loc_rec locations%rowtype; l_loc_json_obj json; l_country_rec countries%rowtype; l_country_json_obj json; l_manager_rec manager_cur%rowtype; l_manager_json_obj json; l_employees_json_arr json_list; l_employee_json_obj json; l_job_rec jobs%rowtype; l_jobs_json_arr json_list; l_job_json_obj json; begin select * into l_dept_rec from departments where department_id = get_dept_pljson.p_dept_id; l_dept_json_obj := json(); l_dept_json_obj.put('id', l_dept_rec.department_id); l_dept_json_obj.put('name', l_dept_rec.department_name); select * into l_loc_rec from locations where location_id = l_dept_rec.location_id; l_loc_json_obj := json(); l_loc_json_obj.put('id', l_loc_rec.location_id); l_loc_json_obj.put('streetAddress', l_loc_rec.street_address); l_loc_json_obj.put('postalCode', l_loc_rec.postal_code); select * into l_country_rec from countries cou where cou.country_id = l_loc_rec.country_id; l_country_json_obj := json(); l_country_json_obj.put('id', l_country_rec.country_id); l_country_json_obj.put('name', l_country_rec.country_name); l_country_json_obj.put('regionId', l_country_rec.region_id); l_loc_json_obj.put('country', l_country_json_obj); l_dept_json_obj.put('location', l_loc_json_obj); open manager_cur(l_dept_rec.manager_id); fetch manager_cur into l_manager_rec; if manager_cur%found then l_manager_json_obj := json(); l_manager_json_obj.put('id', l_manager_rec.employee_id); l_manager_json_obj.put('name', l_manager_rec.first_name || ' ' || l_manager_rec.last_name); l_manager_json_obj.put('salary', l_manager_rec.salary); select * into l_job_rec from jobs job where job.job_id = l_manager_rec.job_id; l_job_json_obj := json(); l_job_json_obj.put('id', l_job_rec.job_id); l_job_json_obj.put('title', l_job_rec.job_title); l_job_json_obj.put('minSalary', l_job_rec.min_salary); l_job_json_obj.put('maxSalary', l_job_rec.max_salary); l_manager_json_obj.put('job', l_job_json_obj); l_dept_json_obj.put('manager', l_manager_json_obj); else l_dept_json_obj.put('manager', hr.json_value.makenull()); end if; close manager_cur; l_employees_json_arr := json_list(); for emp_rec in ( select * from employees where department_id = l_dept_rec.department_id ) loop l_employee_json_obj := json(); l_employee_json_obj.put('id', emp_rec.employee_id); l_employee_json_obj.put('name', emp_rec.first_name || ' ' || emp_rec.last_name); l_employee_json_obj.put('isSenior', emp_rec.hire_date < to_date('01-jan-2005', 'dd-mon-yyyy')); l_employee_json_obj.put('commissionPct', emp_rec.commission_pct); l_jobs_json_arr := json_list(); for jh_rec in ( select job_id, department_id, start_date, end_date from job_history where employee_id = emp_rec.employee_id ) loop l_job_json_obj := json(); l_job_json_obj.put('id', jh_rec.job_id); l_job_json_obj.put('departmentId', jh_rec.department_id); l_job_json_obj.put('startDate', to_char(jh_rec.start_date, l_date_format)); l_job_json_obj.put('endDate', to_char(jh_rec.end_date, l_date_format)); l_jobs_json_arr.append(l_job_json_obj.to_json_value); end loop; l_employee_json_obj.put('jobHistory', l_jobs_json_arr); l_employees_json_arr.append(l_employee_json_obj.to_json_value()); end loop; l_dept_json_obj.put('employees', l_employees_json_arr); dbms_lob.createtemporary(l_dept_json_clob, true); l_dept_json_obj.to_clob(l_dept_json_clob); return l_dept_json_clob; exception when others then if manager_cur%isopen then close manager_cur; end if; raise; end get_dept_pljson; |
Output
When passed a department id of 10, the function returns a CLOB populated with JSON that matches the goal 100%.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
{ "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
All in all, I really like PL/JSON. Having been in the open source community for so long now it’s grown into a fairly robust and stable library. Also, because it’s open source, it can be downloaded and installed in any database. I tested it in an 11g XE instance without any issues and I believe it would work fine in 10g too.
That’s not to say PL/JSON isn’t without its faults. PL/JSON can take some time to learn and get used to. For example, on line 141 of the solution above, you’ll see an employee JSON object being appended to the employees JSON_LIST. However, the JSON object can’t be appended directly, you must append the result of a call to to_json_value() instead. But the same is not true for the put method, which will take JSON or JSON_VALUE types. I’m sure there’s a good reason for this, but it’s still confusing.
Another problem with PL/JSON is the documentation. The homepage on GitHub says, “To learn the API, look at the files in /examples”. The examples are fine, but proper API documentation would be very welcome and benefit the entire community. Anyone feel like submitting some pull requests? 🙂
Finally, I’d heard complaints about performance issues with PL/JSON so I decided to run a test. On a local 18c XE database, I generated the JSON for each department in the HR schema 100 times. The PL/JSON solution took about 4.6 seconds on average while the solution in this post and the APEX_JSON solution both took around 1.5 seconds.
Post Oracle Database 12.1+ issues
If you use PL/JSON with Oracle Database 12.1+, then you may run into some small issues. I’ll list any issues I identify here.
- JSON_VALUE – PL/JSON installs a synonym named JSON_VALUE, which points to a type named PLJSON_VALUE. Unfortunately, JSON_VALUE happens to be the name of function added to Oracle Database 12.1 for quering JSON values from documents. You may get the following error if the compiler picks up on the built-in when you intend to use PL/JSON’s version: Error(98,48): PLS-00103: Encountered the symbol “.” when expecting one of the following: ( . The work around is simple enough, just prefix JSON_VALUE with the schema name where PL/JSON was installed (as was done on line 98 in the solution above) or reference the PLJSON_VALUE type directly.
Thank you for the great article. It is true, documentation is a big sore spot and it’s one I’m working on at least improving for an upcoming 2.0.0 release.
And as you said, we are always open to a pull request 🙂
(I’m a PL/JSON maintainer.)
In the above example the author assumed all the values exists. What if a value is blank. How do i use the put function to pass null value ?
“jobHistory”: [
{
“id”: “AD_ASST”,
“departmentId”: null,
“startDate”: “17-SEP-1995”,
“endDate”: “17-JUN-2001”
}
Hi Vikr,
Checkout line 98 from the first code sample above. Does that help?
Regards,
Dan
Thank you for a good example. I’m sharing the URL with our team.