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()FOR indx IN 0 .. my_array.get_size() - 1JSON 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 programThe 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
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;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;- 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.
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;
/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;
/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;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;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;
/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.
Comments
Post a Comment