Relational to JSON with PL/JSON

By July 8, 2015 Uncategorized

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 installs 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…

Output

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

Summary

All in all, I really like PL/JSON. Having been in the open source community for so long now it’s grown in to 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 against APEX_JSON 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.

4 Comments

  • James Sumners says:

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

    • Vikr says:

      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”
      }

  • Mark Anderson says:

    Thank you for a good example. I’m sharing the URL with our team.

Leave a Reply