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:
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:
[1]
[1,2,"three"]
[{"object":1},{"inside":2},{"array":3}]
[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):
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:
You can verify this with the following block:
|
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:
But a formulation consistent with iteration through a PL/SQL nested table, such as
is likely to result in this error:
|
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
|
35 | If the element in this index of the array is itself an array, call the json_array_ Traversal procedure recursively, passing
|
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
Photography by Anthony Garand, Unsplash
Steven Feuerstein was Oracle Corporation's Developer Advocate for PL/SQL between 2014 and 2021. He is an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media), and currently serving as Senior Advisor for insum Solutions. Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).