Application Development

Working with JSON Arrays in PL/SQL

Use the PL/SQL JSON_ARRAY_T object type to construct and manipulate in-memory JSON arrays.

By Steven Feuerstein

May/June 2018

As explored in my last Oracle Magazine article, Oracle Database 12c Release 2 adds several predefined object types to PL/SQL to enable 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.

You can use the new PL/SQL 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
  • Transform existing JSON data
  • Create JSON data by using complex rules that reflect the kind of per-row variation you can find in document-oriented applications

The PL/SQL JSON object types available for use are JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T, and JSON_SCALAR_T. This article explores the JSON_ARRAY_T type in more detail.

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, where nested tables and varrays start at index value 1. So the above array has elements defined at index values 0 and 1, not 1 and 2. The ordering of elements in an array is also significant.

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

  • An array containing a single scalar value
    [1]
    
  • An array containing three scalar values
    [1,2,"three"]
    
  • An array containing three JSON objects
    [{"object":1},{"inside":2},{"array":3}]
    
  • An array containing a Boolean literal, an array of scalars, and an object
    [true,
     [1,2,3],
     {"name":"steven"},
    ]
    

How Big Is My Array?

The JSON_ARRAY_T object type offers a get_size method that returns the number of elements in the array. This method can iterate through all the elements of an array.

In the following block, I get the number of elements in the array and then use a loop to determine how many elements are in each array within that array.

DECLARE
   l_stuff        json_array_t;
BEGIN
   l_stuff :=
      json_array_t ('[
        ["Stirfry", "Yogurt", "Apple"],
        ["carpet", "rug", "tiles", "dirt", "concrete"],
        ["smile", "frown", "grimace", "puzzled"]
       ]');

   DBMS_OUTPUT.put_line (
      'Number of elements in array: ' || l_stuff.get_size ());

   FOR indx IN 0 .. l_stuff.get_size - 1
   LOOP
      DBMS_OUTPUT.put_line (l_stuff.get (indx).get_size ());
   END LOOP;
END;

Here’s the output:

Number of elements in array: 3
3
5
4

If you’ve been working with PL/SQL as your primary language, you will be in the habit of writing a FOR loop on a nested table as follows:

FOR indx IN 1 .. l_stuff.COUNT

If you write the same thing for a JSON array, your code will raise an error. This is the one I’ve seen most—and it took me a little while and some tracing, the first time, to realize that that’s what this error meant.

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

Build Your Own Array

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

The JSON_ARRAY_T object type offers several member procedures to BYOA (build your own array):

  • APPEND. Appends a new item on the end of the array
  • APPEND_NULL. Appends a null on the end of the array
  • PUT. Adds or modifies an element at a specified position in the array
  • PUT_NULL. Sets the value of an element at a 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; check out the Live SQL script at the end of this article for the full implementation.)

In the to_JSON package, 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 to_JSON 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 the key and the value. The following table explains the to_array function, line by line:

Line Description
10–11 Accept an associative array and return a JSON array object type instance.
16 Because this is a string-indexed collection, I cannot use a FOR indx 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.
21 Call the JSON_OBJECT_T append member method to add an element to the end of the JSON array. What am I adding? I’m adding a JSON object constructed from the associative array index and item, using the to_json.to_object function.
25 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.
28 Finally, return the JSON array.

Let’s run this conversion function through its paces. In the following block, I take advantage of the new-in-Oracle Database 18c qualified expression feature, enabling 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"}]

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

Remove Elements from an Array

When it comes to JSON arrays, what PL/SQL giveth, PL/SQL can also taketh away. You can use the remove method to remove an element from an array at a specific location.

In the following block, I remove any element from the array of strings if it cannot be converted to a number.

DECLARE
   l_nums   json_array_t := json_array_t ('["123","123.456","abc","19e10"]');
BEGIN
   FOR indx IN REVERSE 0 .. l_nums.get_size - 1
   LOOP
      IF l_nums.get_number (indx) IS NULL
      THEN
         l_nums.remove (indx);
      END IF;
   END LOOP;
   DBMS_OUTPUT.put_line (l_nums.stringify ());
END;
/

After execution, I see this text displayed:

["123","123.456","19e10"]

This block takes advantage of the fact that the default mode for error handling with PL/SQL JSON object type methods is to return NULL if there is an error. So I just check to see if the result of get_number is NULL.

If I “escalate” error handling in this same block by putting this line

l_nums.on_error (1);

right after the BEGIN, it will fail with this unhandled exception when I run the block:

ORA-40566: JSON path expression selected a value of different data type.

Recursive Looping Through an Array

Some JSON arrays are simple lists of scalars or even objects. But many arrays have other arrays within them. 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.

First I create a put_line 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 following json_array_traversal procedure.

 CREATE OR REPLACE PROCEDURE json_array_traversal (
   json_document_in   IN CLOB,
   leaf_action_in     IN VARCHAR2,
   level_in           IN INTEGER DEFAULT 0)
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;

The following table explains the points of interest in the json_array_traversal procedure, line by line.

Line Description
2–4 Pass in a CLOB containing a JSON document, which, for this procedure, should be an array. The actual value for the “leaf action” parameter (leaf_action_in) is a dynamic PL/SQL block to be executed when a leaf is encountered. It is unlikely that you would use anything this generic in production code, but it could be very handy as a utility.
6–9 Define a number of instances of JSON object types: an array, object, key list, and element.
11 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 will be 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;
13 Display the document passed in, taking advantage of the stringify method.
15 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
19 Because an element in an array can be a scalar, object, or another array, provide a WHEN clause for each possibility. Well, not each and every possibility. There are more types of scalars than string, but I leave the expansion of the CASE statement to cover all scalar types.
20–23 If the element is a scalar string, use native dynamic SQL to execute the provided PL/SQL block. I pass 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).
24 Handle the element, which is a JSON object.
26 Cast the array element into a JSON object type instance.
28 Get the names of all the keys in the object.
30–34 Call 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"]
35 If the element in this index of the array is itself an array, call the json_array_ 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 this 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 this 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:

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

A Ray of Sunshine with JSON Arrays

JSON arrays are widely and heavily used. They are also extremely flexible, because 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.

Next Steps

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

READ 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:
JSON array traversal
The to_json package

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.