Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to use JSON array in PL/SQL" related knowledge, editor through the actual case to show you the operation process, the method of operation is simple and fast, practical, I hope that this "how to use JSON array in PL/SQL" article can help you solve the problem.

Like a class, an object type provides 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 a few things to keep in mind about using JSON elements and JSON arrays in PL/SQL:

Error handling behavior

By default, if an error occurs when calling a member method for an JSON array (or object), NULL is returned. In other words, the exception does not throw back your block.

If you want the error to propagate from the method as an exception, call the ON_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 at 1 instead of 0. Using an associative array, it can start anywhere you want. : -)

JSON array indexing starts at 0, which is common in many other programming languages, and we follow this convention by using JSON arrays in Oracle databases. So you don't want to iterate through an 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 ()-1JSON array basis

An array is a comma-separated list of elements within square brackets, as follows:

["SQL", "PL/SQL"]

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

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

The order of elements in an array is important, and unlike the order of objects, the order of members of objects is not important (similar to relational tables).

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

1. An array containing a single scalar value

[1]

two。 An array containing three scalars

[1pr 2, "three"]

3. An array of three JSON objects

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

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

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

Build your own array

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

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

APPEND-append a new item to the end of the array

APPEND_NULL-append a new item to the end of the array

PUT-adds or modifies an element at a specified location in the array

PUT_NULL-sets the element value at the specified location in the array to NULL

To demonstrate append, I created a "to JSON" package that converts a string-indexed associative array into an JSON array (it also contains other "to JSON" functions; try it yourself with this LiveSQL script).

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 wrapper specification; note that the associative array is indexed by the subtype INDEX_T defined as VARCHAR2 (50).

PACKAGE to_json AUTHID DEFINERIS 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 package:

PACKAGE BODY to_jsonIS 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 lumbjson array; END;END

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

Accepts an associative array and returns an instance of the JSON array object type.

Since this is a string index collection, 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 the JSON_OBJECT_T append member method to add an element to the end of the JSON array. What do I want to add? A JSON object constructed from an associative array index and item using the to_json.to_object function.

Find the next defined index value (remember: string! ). The NEXT function returns NULL when it passes the last index value, which stops the WHILE loop.

Returns the JSON array.

It's time to run some code!

In the block below, I take advantage of the new-to-18c qualifying expression feature, which allows me to initialize the contents of an indexed array with a single expression. Then I 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;/

The results are as follows:

Appending andthen: someWatch it grow! 1Appending can:inWatch it grow! 2Appending fullstop:Watch it grow! 3Appending oracledatabase:18cWatch it grow! 4Appending yes:youWatch 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 into a string index collection.

Recursive loop traversal array

Some JSON arrays are simple lists of scalars or even objects. But many arrays contain other arrays. With 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 establish a process to do this.

All the code in this section can be found, run, and used on LiveSQL.

First, I'll create a helper to display the string and indent it 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) ISBEGIN DBMS_OUTPUT.put_line (LPAD ('', pad_in * 3) | | string_in); END;/

My version of DBMS_OUTPUT.put_line is used in multiple places during the json_array_traversal 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: = FOR k_index IN. 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 that contains the JSON document, which should be an array for this process. The actual value of the Leaf Action parameter is the dynamic PL/SQL block to be executed when a leaf is encountered. You are unlikely to use any of this generic stuff in production code, but it can be very convenient as a utility.

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

Parses the document (text) into a hierarchical memory representation. At this point, if json_document_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.

Iterate through each element in the array. The get_size method returns the number of elements in the array. Remember that the JSON array index starts with zero (0). So it works:

FOR indx IN 0.. L_array.get_size-1

But formulas that are consistent with iterations through PL/SQL nested tables, such as:

FOR indx IN 1.. L_array.get_size

It is likely to lead to this error:

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

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

If the element is a scalar string, then I use the 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 action I choose 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 procedure, passing:

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

two。 Same leaf action dynamic block

3. Level, raise 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: 0Leaf: StirfryIndex: 1Leaf: nameIndex: 2Leaf: MosquitosIndex: 3 Traverse: ["finger", "toe", "nose"] Index: 0Leaf: finger Index: 1Leaf: toe Index: 2Leaf: nose

And through the following call:

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: 0Leaf: StirfryIndex: 1Leaf: nameIndex: 2Leaf: MosquitosIndex: 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: arrayIndex: 4Leaf: elbow on "how to use JSON arrays in PL/SQL" Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.