Oracle Database 12.2.0.1 – PL/SQL JSON functionality

One functionality area in Oracle Database 12.2.0.1 that now also supports the handling of JSON is PL/SQL ! This much anticipated and wanted/used functionality makes it now way easier to handle JSON content via PL/SQL in a database standard and…native (supported)…way.

So, besides using the already shown SQL methods via the post about “Oracle Database 12.2.0.1 – Generating JSON in the Database using SQL/JSON Operators”, you can use these but also now programmatically, now via PL/SQL, create, parse, etc, JSON content in the database.

I hope you like this small overview / insight in the 12.2.0.1 version of the database. Have a go at it via Oracle’s Exadata Express Cloud Service

These API’s make it possible to add, update and remove key:value pairs. The APIs allow you to navigate the structure of the JSON document, in a manner that is very similar to the way in which the XML Document Object Model or DOM enables navigation of an XML document.

A short introduction about these new JSON PL/SQL additions in the 12.2.0.1 database.

JSON PL/SQL Object Types

There are several new PL/SQL Object Types you can use:

• JSON_ELEMENT_T
  • Supertype of the other JSON object types
• JSON_OBJECT_T
  • Subtype used to hold JSON objects
• JSON_ARRAY_T
  • Subtype used to hold JSON arrays
• JSON_SCALAR_T
  • Subtype used for scalar values
• JSON_KEY_LIST
  • Varray of VARCHAR2(4000)

JSON_OJBECT_T and JSON_ARRAY_T extend JSON_ELEMENT_T, so they inherit all of JSON_ELEMENT_T’s methods. Methods like PARSE( ) or STRINGIFY ( ) from JSON_ELEMENT_T can therefore be used for subtypes. PARSE is used to convert textual JSON into an instance of JSON_ELEMENT_T.  STRINGIFY is used to generate textual JSON from a PL/SQL JSON_ELEMENT_T object.

Any JSON document can be represented by combining instances of these objects. All PL/SQL operators that return JSON content will return an instance of JSON_ELEMENT_T. These can be cast to either JSON_OBJECT_T or JSON_ARRAY_T before being operated on.

JSON Methods

Hereby some of the available methods

• JSON Parsing
  • “parse”
    • accepts varchar2, CLOB, BLOB. The output is JSON tekst and can be used in a JSON element
• JSON Serialisation
  • “to_”
    • For example to_string( ) returns a string (varchar2) representation of a JSON object
    • For example to_number( ) returns a number representation of a JSON object
• JSON Getter and Setter methods for JSON_OBJECT_T and JSON_ARRAY_T
  • obtain (“getter”)
    • get( ) returns a JSON reference
    • “get_” returns a JSON copy, for example “get_clob( )”
  • update (“setter”)
    • put ( ) will update an object or array instance
    • put_null ( )  sets JSON object or array to NULL value 
    • append ( )  adds a new element at the end of the array (for JSON_ARRAY only)
• JSON Introspection
  • “is_” checks JSON if it is a JSON object, array, scalar or string, number… (returns boolean)
  • get_size( ) will return the number of members
  • get_type( ) will return the JSON type
  • get_keys( ) will return an instance of JSON_KEY_LIST
• Other
  • remove ( ) will remove the object member or array element
  • clone ( ) will create and return copy of the object or array

Parsing JSON – A simple example

Below a simple example of how you could use this new JSON PL/SQL functionality to handle JSON content via PL/SQL:

Explaining the code a bit …

The “jo” JSON_OBJECT_T object type will get the content, via “parse” of the JSON string content ‘{“name”:”Beda”,”jobTitle”:”codmonki”,”projects”:[“json”, “xml”]}’. Using the JSON_KEY_LIST and .get_keys introspection method, you can determine the first and last items and thus loop trough all available values. While looping through the values, these can now be, via .append( ), transformed into a JSON Array as its result. This JSON Array “ja” can now be transformed to a string via .to_string and assigned to “keys_string” which is a VARCHAR2(100). 

This ends the short introduction about these new JSON PL/SQL additions in the 12.2.0.1 database. Hope you liked it and have a go at it via Oracle’s Exadata Express Cloud Service.

HTH/M