This Blog Posting is about a PL/SQL package, which has recently been introduced with APEX 5.0, and which
is very interesting also for non-APEX Developers: APEX_JSON. This package is based upon the very
popular PL/JSON package and provides PL/SQL support for
parsing and generating JSON. By building own SQL functions, APEX_JSON functionality can be brought
to the SQL layer as well (if you are interested in this, read on).
SQL and PL/SQL developers can find two very nice use-cases for APEX_JSON - outside of APEX:

JSON can now be parsed also in pre-12.1.0.2 databases. In 12.1.0.2 and higher, I'd recommand
to use the new native SQL/JSON functions. JSON_VALUE, JSON_QUERY or JSON_TABLE have been implemented
at SQL level, within the database kernel, and should therefore be more efficient than a PL/SQL solution.
But those who need to implement JSON parsing on 11.2 or 12.1.0.1, can now use APEX_JSON
very nicely for their requirements.

Generating JSON from SQL query results or table data is still not possible with SQL alone - even with 12.1.0.2. Until
this feature arrives, we can do this kind of work with APEX_JSON.

So, in this blog posting, I'll show how JSON can be generated from table data using APEX_JSON - but
at the SQL and PL/SQL layer - without APEX. The next posting will then be about parsing
JSON with APEX_JSON. Let's start easy (as always): We want to generate JSON from the well known
table EMP and for this first example, the ENAME column is sufficient.

At the beginning, we initialize the JSON generator with INITIALIZE_CLOB_OUTPUT. So, the
JSON output will be written to a (temporary) CLOB. Within APEX, there is also the alternative to
write the output to the HTP buffer. INITIALIZE_CLOB_OUTPUT takes three parameters. The first
two are being used to construct the internal temporary LOB, usage is the same as in
DBMS_LOB.CREATETEMPORARY.
The third parameter determines the indentation for the JSON hierarchy.

Of course, APEX_JSON also allows to create more complex JSON hierarchies. It's all about
the nesting of OPEN_OBJECT and OPEN_ARRAY calls. So, the following example generates
"nested JSON" for the tables DEPT and EMP.

Note, that you as the developer are responsible for the correct nesting of your
OPEN_OBJECT and OPEN_ARRAY calls and that you have to call
CLOSE_OBJECT and CLOSE_ARRAY repectively. Opening an object or an array without
closing it leads to a wrong or invalid JSON structure. APEX_JSON provides the
CLOSE_ALL procedure, which is best being called at the end of your procedure; it
will prevent generating JSON with objects unclosed.

It seems that the JSON hierarchy always has to be "coded" with PL/SQL calls in
a procedure or an anonymous block. In practice, we often need something more
"automagically": We want to provide a query - and the generator does the rest.
For this purpose, one overloading of APEX_JSON.WRITE takes a cursor as
parameter. Let's try this out: We build a generic SQL function, taking a Cursor
as its parameter and generating JSON from that cursor with APEX_JSON.

Now we have a function which can take any SQL query to generate JSON from its results. And
we can use this query in the SQL layer - no PL/SQL needed from here on. With this function you
should be able to take out a large amount of PL/SQL "JSON generator" code. This function does
it all.

The JSON structure coming out of this function will always be as flat as a SQL query result is.
In some cases, more complex JSON with a hierarchy is needed - does that mean we have to
write PL/SQL code again ...?

Interestingly, there is another overloading of APEX_JSON.WRITE. This one takes XMLTYPE as its parameter, and
its purpose is not to simply embed XML to the JSON output (the CLOB variant does this). This
function can do more - and to explore how it works, again, a simple example is needed: Let's model
a simple object structure as XMLTYPE and pass this to APEX_JSON.WRITE.

APEX_JSON examines the XMLTYPE and tries to rebuild the XML structure in the JSON output.
We can use this for the above MAKE_JSON function - the cursor needs to select
an XMLTYPE column, then. Thus, we now create a more complex XML structure for
the data within the EMP table. We use the SQL/XML functions to generate XML and
encapsulate the SQL query in a view.

Passing this query to the MAKE_JSON function leads to a JSON result with a hierarchy according
to the structure of the XMLTYPE column XML. We generated JSON with a hierarchy, but we did
not hard-code it with PL/SQL calls.

APEX_JSON seems to be a but picky regarding the XML structure; for instance, objects with multiple
attributes should be encapsulated with an XML tag; if this is missing, you'll not
get the right JSON structure. You might need to experiment a bit ...

Summarized, I think that the new APEX_JSON package introduced with APEX 5.0, is
very usable outside of APEX as well. The SQL and PL/SQL programmer finds a nice
solution for their "Generating JSON" needs. The option to generate
JSON from a cursor allows very generic solutions - one PL/SQL procedure is
able to generate JSON from multiple tables - always with correct attributes. Bringing
XMLTYPE into the game even allows generic solutions with complex hierarchys. A simple
SQL function taking a cursor and returning a CLOB brings all this functionality to
the SQL layer.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.