Skip to main content

Working With JSON Arrays in PL/SQL

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin 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 varrays 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 an 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 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.
  • 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 is the structure of your JSON array, the most 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.

Resources


 

Comments

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…