This is the second of two blog postings about the new PL/SQL package APEX_JSON, which allows to work with JSON documents in a PL/SQL environment, even outside of APEX. While the
first blog posting was about generating JSON with APEX_JSON, this
posting will concentrate on the parsing side. We already have a bunch of JSON documents and
want to work with their data. Before we begin, here is a very important note.

Beginning with Oracle12c, more specific: 12.1.0.2, the Oracle database provides
native SQL/JSON fucntions zu parse and access JSON data. These are implemented within the
database kernel and therefore much more efficient than a PL/SQL-based approach.
So, in an Oracle12c environment, always try to work with the nativer SQL/JSON functions
instead of programming PL/SQL with APEX_JSON. JSON parsing with APEX_JSON is suited for pre-12c databases,
where the native functions are not available. In this bloh posting, you'll also see a comparison
between the two approaches.

Here we go: As an example, I'll take some JSON files which I obtained from the Twitter API. Here's a snippet.

Assumed, we have a table containing instances of these JSON documents, we can start parsing these
with APEX_JSON.PARSE. APEX_JSON will generate an in-Memory representation of the JSON data. We can then access
the JSON data by calling methods like GET_VARCHAR2 or GET_NUMBER. A first procedure looks like this:

Important: If you are working with the very first APEX 5.0.1 release, and you are using a
german or french NLS environment, execute the following call at the beginning of your session: ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'

In this example, the processing results are being written onto the console with DBMS_OUTPUT. Of course,
we could also process it otherwise or create a table function which returns structured output. So far, so good.

But this is the same situation as described in the first blog posting. We will have to create
procedural code for each individual query requirement. It would be much more convenient, to
execute a SQL-like query direcly on the JSON data. And APEX_JSON offers a trick:
The function TO_XMLTYPE converts JSON to an XMLTYPE instance ...

This is much better - by changing the SQL query we can access any JSON attribute
we want. By nesting XMLTABLE expressions, we can also access hierarchical structures;
this has been an XML requirement for years. Using this powerful tool, we can
take a more sophisticated challenge: "We want to know, how many tweets each user
posted, but without retweets". The query is straightforward.

So, the SQL/XML functions take us a long way in parsing and working with JSON. Without
those functions, we would have to author an awful lot of procedural code. In the following
example, the descending sort is even missing.

Let's have a look on the exeution time: The table contains 4.526 JSON documents. For these
documents, the SQL/XML approach took about 1 minute and 17 seconds, whereas the procedural
approach took 1 minute and 6 seconds. We see, the XML overhead is rather small compared
to the JSON parsing time. Let's compare this to the native SQL/JSON functions in Oracle12c.

We get the same result, but in less than a second! 0.79 Seconds, to be precise. So,
once more: If you are working in an Oracle12c environment, make sure to use the native
SQL/JSON functions when parsing and accessing JSON data. APEX_JSON is suitable when
the SQL/JSON functions are not available (like in Oracle 11.2) or not appropriate (you might
want to use pure PL/SQL and avoid SQL in specific situations).

Summarized, the new APEX_JSON package introduced with APEX 5.0, is
very usable outside of APEX as well. Beyond generating JSON it also allows
o parse JSON documents and to access JSON data. After converting
JSON to XMLTYPE, we can perform even flexible queries and access any JSON attribute
we want - without authoring additional procedural code.

But the native SQL/JSON
functions, which are available in Oracle12c and higher, offer much better query
performance since these have been implemented directly into the database kernel. So,
if possible, JSON parsing is done best with the JSON_VALUE, JSON_QUERY or
JSON_TABLE SQL functions.

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.