Sum of sincerity: how to use JSON arrays in PL/SQL?

Oracle Database 12c version 2 is built based on the 12.1 SQL/JSON feature, adding many built-in object types (similar to classes in object-oriented language) for manipulating JSON data in PL/SQL blocks.

In this article, I explored some ways through JSON_ ARRAY_ Array oriented JSON features provided by type T and its methods.

Like classes, object types provide a predefined constructor to instantiate new instances, static methods, and member methods of the type.

Here are the methods you are most likely to use:

In general, there are several points to remember about using JSON elements and JSON arrays in PL/SQL:

Error handling behavior

By default, NULL is returned if an error occurs when calling a member method for a JSON array (or object). In other words, exceptions are not thrown back to your block.

Call on if you want the error to propagate from the method as an exception_ Error method and pass a value greater than 0.

Array index

In PL/SQL, you may know that indexes in nested tables and variable arrays start with 1, not 0. With associative arrays, it can start anywhere you want.: -)

JSON array index starts from 0, which is common in many other programming languages. We use JSON array in Oracle database to follow this Convention. So you don't want to traverse a JSON array with a loop header, as follows:

FOR indx IN 1 .. my_array.get_size()

Instead, you should write:

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

JSON array Foundation

Array is a comma separated list of elements in square brackets, as follows:

 ["SQL", "PL/SQL"] 

The index of JSON array starts from 0, which is different from the specification of PL/SQL collection (nested tables and arrays start from index value 1).

So the elements of the array shown above are defined in the index values 0 and 1, not 1 and 2.

The order of elements in an array is very important. Unlike the order of objects, the order of members of objects is not important (similar to a relational table).

JSON arrays can contain scalars, objects, and arrays. These are valid JSON arrays:

1. An array containing a single scalar value

 [1] 

2. Array containing three scalars

 [1,2,"three"] 

3. Array of three JSON objects

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

4. An array containing Boolean text, scalar arrays, and objects

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

Build your own array

Sometimes arrays are provided to you and you need to explore (see recursive loop through arrays below). Sometimes you need to construct an array from data in a table or program.

JSON_ ARRAY_ The T type provides many member procedures for BYOA ("build your own array"):

  • APPEND – appends a new item to the end of the array
  • APPEND_NULL – appends a new item to the end of the array
  • PUT - adds or modifies an element at a specified position in the array
  • PUT_NULL – sets the element value at the specified position in the array to NULL

To demonstrate append, I created a "to JSON" package, which converts string index associative arrays into JSON arrays (it also contains other "to JSON" functions This LiveSQL script Try it yourself).

Each element in the returned JSON array 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 position in the array.

This is the packing specification; Note that associative arrays consist of a subtype index defined as VARCHAR2(50)_ T index.

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;

This is the inclusion:

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;

to_ The object function hides all the details of constructing valid JSON objects from keys and values. to_ The array function is explained as follows:

  • Accept an associative array and return an instance of JSON array object type.
  • Since this is a string index set, I cannot use the "for index in 1.. array. Count" method. Instead, I start with the lowest index value defined (retrieved by calling the FIRST function on line 13) and use WHILE LOOP.
  • Call JSON_OBJECT_T append member method to add an element to the end of the JSON array. What do I want to add? Use to_ json.to_ The object function constructs a JSON object from an associative array index and an item.
  • Find the index value of the NEXT definition (remember: string!). The NEXT function returns NULL after the last index value, which stops the WHILE loop.
  • Returns a JSON array.

It's time to run some code!

In the following block, I take advantage of the new-to-18c qualified expression function, which allows me to initialize the contents of the string index array with a single expression. Then I convert it into a JSON array and display the results, all of which are on the DBMS_ OUTPUT.put_ In a single call to 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;
/

The results are as follows:

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 in a different order than they appear in the qualified expression that populates the associative array. This is because values are automatically sorted in character set order when they are placed in the string index set.

Recursive loop traversal array

Some JSON arrays are simple lists of scalars or even objects. However, many arrays contain other arrays. Using these arrays with nested arrays, you may want to traverse all the "leaves" in the hierarchy. The easiest way is to use recursion. Let's build a process to do this.

All the codes in this section can be found in LiveSQL Find, run, and use on.

First, I'll create a helper to display the string, indented to show its position 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 DBMS_OUTPUT.put_line version in json_array_traversal is used in several places in the process, as 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;

This is a narrative description of the code:

Pass in the CLOB containing the JSON document, which should be an array for this procedure. The actual value of the leaf action parameter is the dynamic PL/SQL block to execute when a leaf is encountered. You're unlikely to use any of these generic things in production code, but it can be very convenient as a utility.

Defines instances of multiple JSON object types: arrays, objects, key lists, and elements.

Parses the document (text) into a hierarchical memory representation. At this point, if JSON_ document_ If in is not a valid array, the following error is thrown:

 ORA-40587: invalid JSON type 

You can verify this using the following blocks:

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

OK, then I will display the incoming document using the stringify method.

Traverse each element in the array. get_ The size method returns the number of elements in the array. Remember that the JSON array index starts with zero (0). So this works:

 FOR indx IN 0 .. l_array.get_size – 1 

However, formulas consistent with iteration through PL/SQL nested tables, such as:

 FOR indx IN 1 .. l_array.get_size 

This error is likely to result:

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

The elements in an array can be scalars, objects, or another array. So I provide a WHEN clause for each possibility. Well, not everyone. There are more scalar types than strings, but I leave the extension of the CASE statement to dear readers to cover all scalar types.

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

For an object, I get all its keys, and then perform leaf operations on each key value. Note: This is the operation I chose to perform for the object. In a more complete implementation, you will traverse the value of the object and take specific actions based on the type of value. For example, an object can contain an array as follows:

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

Finally, if it is an array, I recursively call the traversal process and pass:

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

2. Same leaf action dynamic block

3. Level, increase by 1.

When I call traversal, the process is 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 by calling:

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

generalization

JSON arrays are widely used. They are also very flexible because they can contain scalars, objects, and other arrays. The more complex and nested the structure of JSON arrays, the more challenging it is to handle.

JSON_ ARRAY_ The t object type provides a clean and fast API for querying and constructing JSON arrays. Once you can associate PL/SQL arrays with JSON arrays (for example, correcting differences in indexes), you will find it easy to write code in your PL/SQL code to handle JSON arrays.

Tags: SQL IT

Posted on Sun, 26 Sep 2021 00:35:05 -0400 by azsolo