Working With JSON Arrays in PL/SQL

Explore how to make use of JSON arrays when working with PL/SQL.

Steven Feuerstein

Jan. 15, 19 · Tutorial

Like (2)

Save

42.0K Views

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of built-in object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks.

In this post, I explore some of the array-oriented JSON features all made available through the JSON_ARRAY_T type and its methods.

Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods.

Here are the methods you are most likely to use:

A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL:

Error Handling Behavior

By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not raised back to your block.

If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value greater than 0.

Array Indexes

In PL/SQL, as you probably know, indexing in nested tables and varrays starts at 1, not 0. With associative arrays, it can start wherever you want it to start. :-)

JSON array indexing starts at 0, as is common in many other programming languages, and we follow that convention with JSON arrays in the Oracle Database. So you don't want to iterate through a JSON array with a loop header like this:

FOR indx IN 1 .. my_array.get_size()

Instead, you should write something like this:

FOR indx IN 0 .. my_array.get_size() - 1

JSON Array Basics

An array is a comma-delimited list of elements inside square brackets, as in:

["SQL", "PL/SQL"]

The index for a JSON array starts at 0, which is different from the norm for PL/SQL collections (nested tables and arrays start at index value 1).

So the array shown above has elements defined at index values 0 and 1, not 1 and 2.

The ordering of elements in an array is significant, in contrast to objects, in which the ordering of members is not significant (similar to relation tables).

A JSON array can contain scalars, objects and arrays within it. These are all valid JSON arrays:

1. An array containing a single scalar value

[1]

2. An array containing three scalars

[1,2,"three"]

3. An array of three JSON objects

[{"object":1},{"inside":2},{"array":3}]

4. An array containing a Boolean literal, an array of scalars, and an object

[true,[1,2,3],{"name":"steven"},]

Build Your Own Array

Sometimes the array is provided to you, and you need to go exploring (see Recursive Looping Through An Array, below). Sometimes you need to construct an array from data in a table or your program.

The JSON_ARRAY_T type offers a number of member procedures to BYOA ("build your own array"):

APPEND – Append a new item on the end of the array
APPEND_NULL – Append a new item on the end of the array
PUT – Adds or modifies element at specified position in the array
PUT_NULL – sets value of element at specified position in the array to NULL

To demonstrate append, I created a "to JSON" package that converts a string-indexed associative array to a JSON array (it also contains other "to JSON" functions; try it out yourself with this LiveSQL script).

Each element in the JSON array returned is a JSON object in the form

{"index-value":"item-value"}

where index-value is the string index value in the associative array, and item-value is the value of the item at that location in the array.

Here's the package specification; note that the associative array is indexed by a subtype, INDEX_T, which is defined as VARCHAR2(50).

PACKAGE to_json AUTHID DEFINER
IS
   SUBTYPE index_t IS VARCHAR2 (50);

   TYPE assoc_array_t IS TABLE OF VARCHAR2 (100)
      INDEX BY index_t;

   FUNCTION to_object (key_in IN VARCHAR2, value_in IN VARCHAR2)
      RETURN json_object_t;

   FUNCTION to_array (assoc_array_in IN assoc_array_t)
      RETURN json_array_t;
END;

And here's the package body:

PACKAGE BODY to_json
IS
   FUNCTION to_object (key_in IN VARCHAR2, value_in IN VARCHAR2)
      RETURN json_object_t
   IS
   BEGIN
      RETURN json_object_t ('{"' || key_in || '":"' || value_in || '"}');
   END;

   FUNCTION to_array (assoc_array_in IN assoc_array_t)
      RETURN json_array_t
   IS
      l_index        index_t := assoc_array_in.FIRST;
      l_json_array   json_array_t := json_array_t ();
   BEGIN
      WHILE l_index IS NOT NULL
      LOOP
         DBMS_OUTPUT.put_line (
            'Appending ' || l_index || ':' || assoc_array_in (l_index));

         l_json_array.append (to_object (l_index, assoc_array_in (l_index)));

         DBMS_OUTPUT.put_line ('Watch it grow! ' || l_json_array.get_size ());

         l_index := assoc_array_in.NEXT (l_index);
      END LOOP;

      RETURN l_json_array;
   END;
END;

The to_object function hides all the details of constructing a valid JSON object from key and value. The to_array function is explained below:

Accept an associative array, return a JSON array object type instance.
Since this is a string-indexed collection, I cannot use a "FOR index IN 1 .. array.COUNT" approach. Instead, I start with the lowest-defined index value (retrieved on line 13 with a call to the FIRST function) and use a WHILE LOOP.
Call the JSON_OBJECT_T append member method to add an element to the end of the JSON array. What am I adding? A JSON object that is constructed from the associative array index and item, using the to_json.to_object function.
Find the next defined index value (remember: strings!). The NEXT function returns NULL when going past the last index value, and that will stop the WHILE loop.
Return the JSON array.

Time to run some code!

In the following block, I take advantage of the new-to-18c qualified expression feature, allowing me to initialize the contents of a string-indexed array with a single expression. I then convert it to a JSON array, and display the results, all in a single call to DBMS_OUTPUT.put_line:

DECLARE
   l_array to_json.assoc_array_t := 
      to_json.assoc_array_t (
         'yes' => 'you', 'can'=>'in', 'oracledatabase'=>'18c', 
         'fullstop'=>NULL, 'and then'=>'some');
BEGIN
   DBMS_OUTPUT.put_line (to_json.to_array (l_array).to_string ());
END;
/

Here are the results:

Appending and then:some

Watch it grow! 1

Appending can:in

Watch it grow! 2

Appending fullstop:

Watch it grow! 3

Appending oracledatabase:18c

Watch it grow! 4

Appending yes:you

Watch it grow! 5

[{"andthen":"some"},{"can":"in"},{"fullstop":""},{"oracledatabase":"18c"},{"yes":"you"}]

Notice that the items in the JSON array are not in the same order as they appeared in the qualified expression that populated the associative array. That's due to the automatic ordering by character set order when values are put into a string-indexed collection.

Recursive Looping Through An Array

Some JSON arrays are simple lists of scalars or even objects. But many arrays have within them other arrays. And with these arrays-with-nested-arrays, you might want to iterate through all the "leaves" in that hierarchical structure. The easiest way to do that is with recursion. Let's build a procedure to do just that.

All the code in this section may be found, run and played around with on LiveSQL.

First, I will create a helper procedure to display the string, indented to show its place in the JSON array hierarchy:

CREATE OR REPLACE PROCEDURE put_line (
   string_in   IN VARCHAR2,
   pad_in      IN INTEGER DEFAULT 0)
IS
BEGIN
   DBMS_OUTPUT.put_line (LPAD (' ', pad_in * 3) || string_in);
END;
/

My version of DBMS_OUTPUT.put_line is used in several places in the json_array_traversal procedure, shown below.

CREATE OR REPLACE PROCEDURE json_array_traversal ( 
   json_document_in   IN CLOB, 
   leaf_action_in     IN VARCHAR2, 
   level_in           IN INTEGER DEFAULT 0) 
   AUTHID DEFINER 
IS 
   l_array     json_array_t; 
   l_object    json_object_t; 
   l_keys      json_key_list; 
   l_element   json_element_t; 
BEGIN 
   l_array := json_array_t.parse (json_document_in); 

   put_line ('Traverse: ' || l_array.stringify (), level_in); 

   FOR indx IN 0 .. l_array.get_size - 1 
   LOOP 
      put_line ('Index: ' || indx, level_in); 

      CASE 
         WHEN l_array.get (indx).is_string 
         THEN 
            EXECUTE IMMEDIATE leaf_action_in 
               USING l_array.get_string (indx), level_in; 
         WHEN l_array.get (indx).is_object 
         THEN 
            l_object := TREAT (l_array.get (indx) AS json_object_t); 

            l_keys := l_object.get_keys; 

            FOR k_index IN 1 .. l_keys.COUNT 
            LOOP 
               EXECUTE IMMEDIATE leaf_action_in 
                  USING l_keys (k_index), level_in; 
            END LOOP; 
         WHEN l_array.get (indx).is_array 
         THEN 
            json_array_traversal ( 
               TREAT (l_array.get (indx) AS json_array_t).stringify (), 
               leaf_action_in, 
               level_in + 1); 
         ELSE 
            DBMS_OUTPUT.put_line ( 
               '*** No match for type on array index ' || indx); 
      END CASE; 
   END LOOP; 
END;

Here's a narrative description of that code:

Pass in a CLOB containing a JSON document, which for this procedure should be an array. The actual value for the "leaf action" parameter is a dynamic PL/SQL block to be executed when a leaf is encountered. It is unlikely you would use anything this generic in production code, but it could be very handy as a utility.

Define a number of instances of JSON object types: an array, an object, key list, and element.

Parse the document (text) into a hierarchical, in-memory representation. At this point, if json_document_in is not a valid array, the following error is raised:

ORA-40587: invalid JSON type

You can verify this with the following block:

DECLARE
   l_doc CLOB := '{"name":"Spider"}';
BEGIN
   json_array_traversal (
      l_doc,
      q'[BEGIN NULL; END;]');
END;

OK, then I display the document passed in, taking advantage of the stringify method.

Iterate through each element in the array. The get_size method returns the number of elements in the array. Remember that JSON array indexes start with zero (0). So this works:

FOR indx IN 0 .. l_array.get_size – 1

But a formulation consistent with iteration through a PL/SQL nested table, such as:

FOR indx IN 1 .. l_array.get_size

Is likely to result in this error:

ORA-30625: method dispatch on NULL SELF argument is disallowed

An element in an array can be a scalar, object or another array. So I provide a WHEN clause for each possibility. Well, not each and every. There are more types of scalars than string, but I leave the expansion of the CASE statement to cover all scalar type to my dear readers.

If the element is a scalar string, then I use native dynamic SQL to execute the provided PL/SQL block. I pass to the string value (by calling the get_string method for that index value) and the level (so that the entry is properly indented in the output).

For an object, I get all of its keys and then take the leaf action for each of the key values. Note: this is the action I chose to perform for an object. In a more complete implementation, you would iterate through the values of the object, and take specific action depending on the value's type. For example, an object could have an array within it, as in:

{"chicken_noises":["click","clack","cluck"]}

Finally, if an array, I call the traversal procedure recursively, passing:

1. This element, cast to an array, and then converted back to string format.

2. The same leaf action dynamic block

3. The level, raised by 1.

When I call the traversal procedure as follows:

DECLARE
   l_doc   CLOB := 
      '["Stirfry", 
        {"name":"Spider"}, 
        "Mosquitos", 
        ["finger","toe","nose"]
       ]';
BEGIN
   json_array_traversal (
      l_doc,
      q'[BEGIN put_line ('Leaf: '|| :val, :tlevel);  END;]');
END;
/

I see the following output:

Traverse: ["Stirfry",{"name":"Spider"},"Mosquitos",["finger","toe","nose"]]
Index: 0
Leaf: Stirfry
Index: 1
Leaf: name
Index: 2
Leaf: Mosquitos
Index: 3
   Traverse: ["finger","toe","nose"]
   Index: 0
   Leaf: finger
   Index: 1
   Leaf: toe
   Index: 2
   Leaf: nose

And with the following invocation:

DECLARE
   l_doc   CLOB := '["Stirfry", 
        {"name":"Spider"}, 
        "Mosquitos", 
        ["finger",
         "toe",
         [{"object":1},{"inside":2},{"array":3}]
        ],
        {"elbow":"tennis"}
       ]';
BEGIN
   json_array_traversal (
      l_doc,
      q'[BEGIN put_line ('Leaf: '|| :val, :tlevel);  END;]');
END;
/

I see this output:

Traverse: ["Stirfry",{"name":"Spider"},"Mosquitos",["finger","toe",[{"object":1},{"inside":2},{"array":3}]],{"elbow":"tennis"}]
Index: 0
Leaf: Stirfry
Index: 1
Leaf: name
Index: 2
Leaf: Mosquitos
Index: 3
   Traverse: ["finger","toe",[{"object":1},{"inside":2},{"array":3}]]
   Index: 0
   Leaf: finger
   Index: 1
   Leaf: toe
   Index: 2
      Traverse: [{"object":1},{"inside":2},{"array":3}]
      Index: 0
      Leaf: object
      Index: 1
      Leaf: inside
      Index: 2
      Leaf: array
Index: 4
Leaf: elbow

Summary

JSON arrays are widely and heavily used. They are also extremely flexible, as they can contain scalars, objects, and other arrays. The more complex and nested the structure of your JSON array, the more challenging it can be to work with.

The JSON_ARRAY_T object type offers a clean, fast API for interrogating and constructing JSON arrays. Once you are able to correlate PL/SQL arrays with JSON arrays (correcting for differences in indexing, for example), you will find it easy to productively write code to work with JSON arrays in your PL/SQL code.