The requirement was to explore a JSON document - it's not about looking up a very specific value; we want to know
which attributes and values are in the document. Assumed, we have the following JSON document and we want to explore it programmatically - what's in the document?

That is a nice result; we can see the attribute names, parent names and (if we adjust the SQL query) also the attribute values.
But the data types of the JSON attributes are lost: JSON attributes are either numeric, boolean or strings - with the
conversion to XMLTYPE they all became strings - and we cannot get that information any more.

The APEX_JSON package provides procedures and functions to explore an unknown JSON document. The following code implements
a PL/SQL table function which acts as a "JSON explorer": It parses a JSON and returns all found nodes and attributes together
with some context information. Here is the code:

That's it - have fun trying this out. Basically the function walks through the complete JSON hierarchy, but
due to a restriction in APEX_JSON, it can only access up to five nested(!) arrays. Also I did
not take care for the case that the JSON document is an array at the root level (starting with a "["); it only
works for JSON documents which are objects at the root level (starting with a "{").

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.