JSON and PL/SQL: A Match Made in Database

PL/SQL object types for JSON construct and manipulate in-memory JSON data.

By Steven Feuerstein

March/April 2018

Oracle Database 12c Release 1 ushered in a new age of JSON inside Oracle Database through wide-ranging and smart support of JSON documents in SQL. You can use SQL to join JSON data with relational data. You can “project” JSON data relationally, extracting content from within the JSON document, thereby making it available for relational processes and tools. You can even query, right from within the database, JSON data that is stored outside Oracle Database in an external table.

Oracle Database 12c Release 2 added several predefined object types to PL/SQL to allow fine-grained programmatic construction and manipulation of in-memory JSON data. You can introspect it, modify it, and serialize it back to textual JSON data.

Why Use PL/SQL with JSON Data?

If you can query and manipulate JSON data in Oracle Database tables with SQL, why do you also need PL/SQL object types? You can use the object types to programmatically manipulate JSON data in memory, to do things such as the following:

• Check the structure, types, or values of existing JSON data. For example, check whether the value of a given object field satisfies certain conditions.
• Transform existing JSON data. Suppose, for example, your users have decided they want phone numbers to be stored in a different format.
• Create JSON data using complex rules that reflect the kind of per-row variation you can find in document-oriented applications. For example, suppose your table holds items for sale, and the type of item determines the type of data to be stored (for instance, a flammable product must also have safety information).

Overview

The primary PL/SQL JSON object types you will use are JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T, and JSON_SCALAR_T.

Instances of these types are not persistent. Instead, you first read data from a table into the appropriate instances of the object types or construct the instances through parsing and other operations directly in your PL/SQL code. Next, you manipulate the instances as needed, modifying, removing, or adding values. Finally, you convert or serialize them to VARCHAR2 or large object (LOB) data. Then you store that serialized data back into the database table—or pass it along to a consumer of this data, such as a JavaScript-based web application.

As with so many other data structures manipulated in PL/SQL, you don’t have to worry about freeing up the memory associated with these instances. The database will automatically perform garbage collection tasks. Thanks, Oracle Database!

Let’s explore the new PL/SQL JSON functionality in Oracle Database 12.2 through a series of simple examples.

Extract JSON Values

I first create and populate my species table with very simple JSON data:

CREATE TABLE json_species
(
   id     NUMBER NOT NULL PRIMARY KEY,
   info   CLOB CONSTRAINT is_json CHECK (info IS JSON ) )
/

BEGIN
   INSERT INTO json_species
        VALUES (1, '{"name":"Spider"}');

   INSERT INTO json_species
        VALUES (2, '{"name":"Elephant", "trunk_length":"10"}');

   INSERT INTO json_species
        VALUES (3, '{"name":"Shark", "fin_count":"4"}');

   COMMIT;
END;
/

I can use SQL to query the values of specific items within the JSON documents, as follows:

SELECT sp.info.name
  FROM json_species sp

But I can also use the PL/SQL API:

DECLARE
   CURSOR species_cur
   IS
      SELECT id, sp.info
        FROM json_species sp;

   l_info              json_element_t;
   l_document_object   json_object_t;
   l_name              json_element_t;
BEGIN
   FOR rec IN species_cur
   LOOP
      l_info := json_element_t.parse (rec.info);
      l_document_object := TREAT (l_info AS json_object_t);
      l_name := l_document_object.get ('name');

      dbms_output.put_line (l_name.to_string);
   end loop;
END;
/

The JSON_ELEMENT_T.parse method converts the JSON text into a hierarchical structure that can be traversed using methods in the object types.

I then use TREAT to cast the JSON element instance to a JSON object. Next, I use the get method of JSON_OBJECT_T to get the value for the name attribute. Then I use the to_string method to convert that value into a string.

For this very simple requirement of displaying the names, in which I am not going to iterate through the parsed structure, I could also do this:

BEGIN
   FOR rec IN (SELECT sp.info FROM json_species sp 
                ORDER BY sp.info.name)
   LOOP
      dbms_output.put_line (
         json_object_t (rec.info).get ('name').to_string);
   END LOOP;
END;

In this block, I take advantage of the object-oriented ability to string together multiple invocations of methods. First, I call the constructor function for JSON_OBJECT_T, passing it the JSON character large object (CLOB) data. That returns an instance of JSON_OBJECT_T. Then I call its get method, passing it the name of the attribute (name), and then I invoke the to_string method.

Now what if I want to change the names?

Update JSON Values

The first and most important thing to remember about changing the contents of a JSON document stored in a table is that you must replace the entire document. You cannot, through a SQL UPDATE statement, change the values of individual attributes in the document.

Instead, you retrieve the document from the table, make changes to the document’s contents using the PL/SQL API in Oracle Database 12.2 or 12.1 string operations on the JSON text, and then use a SQL UPDATE to replace the entire document.

Let’s go through the steps needed to implement the following requirement for my species table: all names need to be in uppercase. The code for the requirement is in Listing 1.

Listing 1: Use PL/SQL object types for JSON to change all names to uppercase.

DECLARE
   CURSOR species_cur
   IS
      SELECT sp.id, sp.info
        FROM json_species sp
      FOR UPDATE;

   l_species              json_object_t;
   l_species_for_update   json_species.info%TYPE;
   l_current_name         VARCHAR2 (1000);
   l_new_name             VARCHAR2 (1000);
BEGIN
   FOR rec IN species_cur
   LOOP
      l_species := json_object_t (rec.info);

      l_current_name := l_species.get ('name').to_string;

      l_new_name := TRIM (BOTH '"' FROM UPPER (l_current_name));

      l_species.put ('name', l_new_name);

      l_species_for_update := l_species.stringify;

      UPDATE json_species
         SET info = l_species_for_update
       WHERE CURRENT OF species_cur;
   END LOOP;
END;

And here is an explanation of that code, line by line:

As you get more comfortable with these PL/SQL object types for JSON and their methods, you will look at that code and say “We don’t need to do all that, Steven!” And you will be right. You can replace lines 17 though 21 with a single statement:

l_species.put (
  'name',
  TRIM (BOTH '"' FROM 
     UPPER (l_species.get ('name').to_string)));

But when you are first working with these types, you will likely want to trace each step of the transformation to make sure you get it right. Local variables help with that.

As you might expect, there’s more than one method for modifying the contents of a JSON document. PL/SQL object types for JSON also offer the following methods.

• PUT_NULL. This method sets the value of the specified key to NULL. If the key does not exist, a new one is added to the JSON document with the value set to NULL. The following statement sets the value of name to NULL:
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  l_species.put_null ('name');

• RENAME_KEY. This method renames the specified key with the new value. I could, for example, change the key value from name to species_name, as follows:
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  l_species.rename_key ('name', 'species_name');

• REMOVE. This method removes a name-value pair from the document. Suppose I no longer need to keep track of the length of elephant trunks. I can then execute this statement before updating the row in the table:
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  l_species.remove ('trunk_length');

You can also use PUT, PUT_NULL, and REMOVE to modify arrays, which I cover in the next section.

Working with JSON Arrays

Most JSON documents aren’t nearly so simple as the values inserted earlier into the JSON_SPECIES.INFO column. For example, they often contain arrays, indicated by square brackets, as in the following:

BEGIN
   INSERT INTO json_species
        VALUES (10, '{" name":"Ostrich", 
                      "favorite_eats":
                        ["Stirfry", "Yogurt", "Mosquitos"]}');
END;

The very first thing you should accept—and remember—about JSON arrays is that the first index value is 0, not 1 (as is the case for PL/SQL nested tables and varrays).

With that out of the way, let’s look at how to use JSON_ARRAY_T to work with JSON arrays.

Here are some of the methods you will find helpful for array manipulation:

• GET_SIZE returns the number of elements in the array.
• IS_ARRAY returns TRUE if the instance is an array.
• APPEND appends a new item at the end of the array.
• APPEND_NULL appends a NULL at the end of the array.
• PUT adds or modifies an element at the specified position in the array.
• PUT_NULL sets the value of an element at the specified position in the array to NULL.
• REMOVE removes an element from an array at a specified position. Note: positions are automatically rearranged after a remove operation.

Here are a number of examples using these methods. In each example, assume that the block is executed from within an outer block that declared and populated a JSON object instance as follows:

DECLARE
   l_ostrich   json_object_t;
   l_eats      json_array_t;
BEGIN
   l_ostrich :=
      json_object_t (
         '{"name":"Ostrich", 
                      "favorite_eats":
                        ["Stirfry", "Yogurt", "Mosquitos"]}');
   ...example block here...
END;

1. Get the number of elements in the array.
   Copy code snippet

   Copied to Clipboard

   Error: Could not Copy

   Copied to Clipboard

   Error: Could not Copy

   DECLARE
      l_eats      json_array_t;
   BEGIN
      l_eats := TREAT (l_ostrich.get ('favorite_eats') AS json_array_t);
   
      DBMS_OUTPUT.put_line ('# of eats = ' || l_eats.get_size);
   END;

   I get the value for favorite_eats from l_ostrich and cast it to an instance of the JSON_ARRAY_T type. I then call the get_size method to get the number of elements in the array.
2. Determine whether the name-value pair is an array.
   Copy code snippet

   Copied to Clipboard

   Error: Could not Copy

   Copied to Clipboard

   Error: Could not Copy

   DECLARE
      l_eats   json_array_t;
   BEGIN
      IF l_ostrich.get ('favorite_eats').is_array ()
      THEN
         DBMS_OUTPUT.put_line ('favorite eats is an array');
      END IF;
   
      IF NOT l_ostrich.get ('name').is_array ()
      THEN
         DBMS_OUTPUT.put_line ('name is NOT an array');
      END IF;
   END;

   Here’s the output from this block:
   Copy code snippet

   Copied to Clipboard

   Error: Could not Copy

   Copied to Clipboard

   Error: Could not Copy

   favorite eats is an array
   name is NOT an array

3. Add to the end of the array.
   Append a non-null value, and then use the stringify method to convert the array to a string.
   Copy code snippet

   Copied to Clipboard

   Error: Could not Copy

   Copied to Clipboard

   Error: Could not Copy

   BEGIN 
      l_eats.APPEND ('Truffles');
      DBMS_OUTPUT.put_line (l_eats.stringify());
   END;
   /
   
   ["Stirfry","Yogurt","Mosquitos","Truffles"]

   Append a null value and display the string value.
   Copy code snippet

   Copied to Clipboard

   Error: Could not Copy

   Copied to Clipboard

   Error: Could not Copy

   BEGIN 
      l_eats.APPEND_NULL;
      DBMS_OUTPUT.put_line (l_eats.stringify());
   END;
   /
   
   ["Stirfry","Yogurt","Mosquitos",null]

4. Change the value of an element in the array.
   The following block combines several put-related actions.
   Copy code snippet

   Copied to Clipboard

   Error: Could not Copy

   Copied to Clipboard

   Error: Could not Copy

   BEGIN
      /* Add Ice Cream before Yogurt.
         Remember: arrays start at 0 */
      l_eats.put (1, 'Ice Cream');
      DBMS_OUTPUT.put_line (l_eats.stringify());
      
      /* Add a null value before Ice Cream */
      l_eats.put_null (1);
      DBMS_OUTPUT.put_line (l_eats.stringify());
      
      /* Replace that null with Broccoli */
      l_eats.REMOVE (1);
      l_eats.put (1, 'Broccoli');
      DBMS_OUTPUT.put_line (l_eats.stringify());
   END;
   /

   Here’s the output from this block:
   Copy code snippet

   Copied to Clipboard

   Error: Could not Copy

   Copied to Clipboard

   Error: Could not Copy

   ["Stirfry","Ice Cream","Yogurt","Mosquitos"]
   ["Stirfry",null,"Ice Cream","Yogurt","Mosquitos"]
   ["Stirfry","Broccoli","Ice Cream","Yogurt","Mosquitos"]

Notice that to change a value in an array, I had to first remove it, and then I put a new value in its place.

Error Handling with JSON Types

When it comes to handling errors, the behavior of the JSON types is a bit different from the default behavior in PL/SQL. Generally, if an operation in SQL or PL/SQL results in an error, an exception is raised, halting execution of the block.

You have more flexibility when it comes to the JSON types. First of all, the default behavior is that if an error occurs when an operation calls a member function (a function called for the instance of a type versus a static function, which is invoked on the type itself), that function returns a NULL value.

You can change this behavior by calling the on_error method and passing it one of the following values:

In the following block, I rely on the default behavior. As a result, Number = is displayed, and no error is raised.

DECLARE
   l_fav   json_object_t;
   l_num   NUMBER;
BEGIN
   l_fav := json_object_t ('{"favorite_flavor":"chocolate"}');
   
   /* The default */
   l_fav.on_error (0);
   
   l_num := l_fav.get_number ('favorite_flavor');
   
   DBMS_OUTPUT.put_line ('Number = ' || l_num);
END;

Now I set the error handling value to 1 in the on_error method to raise all errors. When I execute this block:

DECLARE
   l_fav   json_object_t;
   l_num   NUMBER;
BEGIN
   l_fav := json_object_t ('{"favorite_flavor":"chocolate"}');
   
   /* Raise all errors */
   l_fav.on_error (1);
   
   l_num := l_fav.get_number ('favorite_flavor');
END;

I see the following error:

ORA-40566: JSON path expression selected a value of different data type.
ORA-06512: at "SYS.JDOM_T", line 418
ORA-06512: at "SYS.JSON_OBJECT_T", line 256
ORA-06512: at line 10

Now, what if I try to access an array position that is out of bounds?

DECLARE
   l_eats      json_array_t;
BEGIN
   l_eats := JSON_ARRAY_T ('["Stirfry", "Yogurt", "Mosquitos"]');
   
   /* Raise all errors */
   l_eats.on_error (4);
   
   l_eats.put (-1, 'Ice Cream');
   
   DBMS_OUTPUT.PUT_LINE (l_eats.get_size());
END;

I see the following error:

ORA-40578: invalid or non-existent array position
ORA-06512: at "SYS.JDOM_T", line 255
ORA-06512: at "SYS.JSON_ARRAY_T", line 192
ORA-06512: at line 9

Chasing JSON to a Conclusion

Almost every new application being built today, even those constructed in Oracle Application Express, rely heavily on JavaScript and JSON. It’s easy for front-end developers to think that Oracle Database is good only for “relational data” and that if they want data in a JSON format, they need to look elsewhere, perhaps in a document or NoSQL database.

This is definitely and demonstrably not the case, especially with Oracle Database 12.2.

Database developers should learn about JSON in Oracle Database and get really good at building APIs with PL/SQL packages and Oracle REST Data Services. That way, they can provide all the JSON their front-end developers need while ensuring a high level of performance and security for application data.

This article gives you a starting point for manipulating JSON in PL/SQL. Future articles will explore the topic in greater detail.

Next Steps

READ more about Oracle Database support for JSON (documentation).

READ more about Steven Feuerstein’s blog post on getting started with JSON in Oracle Database.

LEARN more about Oracle Database support for JSON (tutorial).

EXPLORE code used in this article at Oracle’s Live SQL.

Photography by © Hero/Corbis