What PL/SQL Libraries For Auto-Generating JSON Do You Recommend?

@Geoff-

The system, which has been in production for 8+ years, was architected to use PL/SQL for the CRUDs and most of the business logic. The PL/SQL also generates 90% of the presentation layer (HTML/JavaScript), using mod PL/SQL. The other 10% is report data done via Oracle Reports Builder.

So, there isn't application code like you'd see in more modern, better architected systems. I do want to do things the right way, I just don't have that luxury given organizational constraints.

I wonder why you don't want to bring the data from Oracle into some application code and make JSON there?

Ouch - generating your interface in PL/SQL. You have my sympathy.

I've never done anything like this, but Googling found this page (which is also referenced from the json.org page).

A relatively new library called PLJSON (no slash) is on GitHub. We're using it in a pretty large project in production and have had no troubles with it at all. Parsing is a tad slow, but that is to be expected.

Disclaimer: I wrote it. If you find bugs or have suggestions, let me know.

In case that anyone is still interested in serving JSON using PL/SQL, I have just completed a PL/SQL data service framework named BackLogic. It is a full REST web service framework. It include a SQL utility to produce complex JSON structure from REF CURSOR, including the "complex tree-like JSON data structure required by a JavaScript tree component" mentioned in the original question, which the early PLJSON framework is not quite capable of doing.

I do see a bright future for PL/SQL in creating REST APIs. Until recently, the Object Relation Impedance has been taken care mainly by ORM frameworks in the middle tier. BackLogic solves this issue in the database, and thus is able to produce complex JSON structures needed by UI framework. Here is a link to BackLogic User Guide. You may find some non-trial examples in Section 5.3.