◉ Introduction
In Oracle Database 12c Release 1 (12.1) the JSON functionality was focused on consuming JSON data and converting it into relation data. Generation of JSON relied on string handling or packages such as the APEX_JSON package. Oracle Database 12c Release 2 (12.2) includes new JSON object types to support the in-memory parsing, generation and update of JSON data directly from PL/SQL.
◉ JSON_ELEMENT_T : The supertype some of the other object types extend. You will not often use this type directly. You can cast a JSON_ELEMENT_T to a subtype using TREAT AS. For example, "l_obj := TREAT (l_elem AS JSON_OBJECT_T);"
◉ JSON_OBJECT_T : An object representing a JSON object.
◉ JSON_ARRAY_T : An object representing a JSON array.
◉ JSON_SCALAR_T : A scalar value associated with a key such as a string, number, boolean or NULL.
◉ JSON_KEY_LIST : An array of key names, typically returned by the GET_KEYS method.
At the time of writing the documentation is full of mistakes, which the documentation team are now aware of. I would suggest regularly crosschecking the documentation against the object definitions in the database, which you can get by describing them ie. "DESC JSON_OBJECT_T".
◉ Constructors
You will typically create new instances of the object types in one of three ways.
SET SERVEROUTPUT ON
DECLARE
l_obj JSON_OBJECT_T;
BEGIN
-- New empty object using constructor.
-- With or without the NEW keyword.
l_obj := JSON_OBJECT_T();
DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
l_obj := NEW JSON_OBJECT_T();
DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
-- New object based on some JSON text using constructor.
-- With or without the NEW keyword.
l_obj := JSON_OBJECT_T('{ "employee_no":9999 }');
DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
l_obj := NEW JSON_OBJECT_T('{ "employee_no":9999 }');
DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
-- New object based on some JSON text using PARSE method.
l_obj := JSON_OBJECT_T.parse('{ "employee_no":9999 }');
DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
END;
/
l_obj.stringify = {}
l_obj.stringify = {}
l_obj.stringify = {"employee_no":9999}
l_obj.stringify = {"employee_no":9999}
l_obj.stringify = {"employee_no":9999}
PL/SQL procedure successfully completed.
SQL>
◉ Serialization Methods
From a JSON perspective, serialization means converting an object to a string, but in PL/SQL it means converting it to another data type. The contents of the JSON object types can be serialized using member functions or procedures, which support conversion to several data types. If the value being returned does not conform to the specific data type required, a conversion is attempted. The STRINGIFY and TO_STRING functions do the same thing, but the former will be more familiar to JavaScript developers.
MEMBER FUNCTION STRINGIFY RETURNS VARCHAR2
MEMBER FUNCTION TO_STRING RETURNS VARCHAR2
MEMBER FUNCTION TO_BOOLEAN RETURNS BOOLEAN
MEMBER FUNCTION TO_NUMBER RETURNS NUMBER
MEMBER FUNCTION TO_DATE RETURNS DATE
MEMBER FUNCTION TO_TIMESTAMP RETURNS TIMESTAMP
MEMBER FUNCTION TO_CLOB RETURNS CLOB
MEMBER PROCEDURE TO_CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
C CLOB IN/OUT NOCOPY
MEMBER FUNCTION TO_BLOB RETURNS BLOB
MEMBER PROCEDURE TO_BLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
B BLOB IN/OUT NOCOPY
◉ Introspection Methods
The JSON object types include type-introspection member functions that can be used to identify their contents.
MEMBER FUNCTION IS_OBJECT RETURNS BOOLEAN
MEMBER FUNCTION IS_ARRAY RETURNS BOOLEAN
MEMBER FUNCTION IS_SCALAR RETURNS BOOLEAN
MEMBER FUNCTION IS_STRING RETURNS BOOLEAN
MEMBER FUNCTION IS_NUMBER RETURNS BOOLEAN
MEMBER FUNCTION IS_BOOLEAN RETURNS BOOLEAN
MEMBER FUNCTION IS_TRUE RETURNS BOOLEAN
MEMBER FUNCTION IS_FALSE RETURNS BOOLEAN
MEMBER FUNCTION IS_NULL RETURNS BOOLEAN
MEMBER FUNCTION IS_DATE RETURNS BOOLEAN
MEMBER FUNCTION IS_TIMESTAMP RETURNS BOOLEAN
MEMBER FUNCTION GET_SIZE RETURNS NUMBER
With the exception of the GET_SIZE method they should all be self explanatory. Depending on the object type, the GET_SIZE method returns the following.
- JSON_SCALAR_T : Returns 1
- JSON_OBJECT_T : Returns the number of top-level keys.
- JSON_ARRAY_T : Returns the number of array elements.
◉ Error Handling
By default any problems when calling a member function result in a return value of NULL. This default behaviour can be altered by the ON_ERROR procedure which accepts one of the following values.
- 0 : Return NULL instead of raising an error (default).
- 1 : Raise all errors.
- 2 : Raise error if no value found.
- 3 : Raise error is return value doesn't match required data type.
- 4 : Raise error for invalid inputs.
The example below deliberately causes a data type conversion error, attempting to convert an employee name into a number. The first conversion error is ignored and a NULL is returned. After switching the error handling to "1", the error is raised.
SET SERVEROUTPUT ON
DECLARE
l_obj JSON_OBJECT_T;
BEGIN
l_obj := JSON_OBJECT_T('{ "employee_name":"CLARK" }');
-- Default : l_obj.on_error(0);
DBMS_OUTPUT.put_line('Error 0 : l_obj.get_number = ' || l_obj.get_number('employee_name'));
l_obj.on_error(1);
DBMS_OUTPUT.put_line('Error 1 : l_obj.get_number = ' || l_obj.get_number('employee_name'));
END;
/
Error 0 : l_obj.get_number =
DECLARE
*
ERROR at line 1:
ORA-40566: JSON path expression selected a value of different data type.
ORA-06512: at "SYS.JDOM_T", line 418
ORA-06512: at "SYS.JSON_OBJECT_T", line 256
ORA-06512: at line 8
SQL>
Each object can have its error handling set separately.
◉ Traversing and Amending JSON
The JSON object types include several member functions and procedures that allow JSON data to be traversed and amended. These member functions and procedures vary depending on the JSON object type.
The get style methods are summarised below.
◉ GET : Returns the JSON_ELEMENT_T associated with the specified key.
◉ GET_OBJECT : Returns the JSON_OBJECT_T associated with the specified key.
◉ GET_ARRAY : Returns the JSON_ARRAY_T associated with the specified key.
◉ GET_* : When applied to an object or array, it returns the data type specified in the method name associated with the specified key. Some JSON object types support calls to GET_STRING without a key. There is a member function and procedure for GET_CLOB and GET_BLOB.
◉ CLONE : Creates a full copy of the object specified by the key.
The following methods are used to amend the JSON data.
◉ PUT : For an object, amend the specified key with the specified value, or create it if it is missing. For an array, add a new element in the specified position, making room if the current index already exists. The examples below will demonstrate this difference.
◉ PUT_NULL : For an object, set the specified key value to NULL, or create it if it is missing. For an array, add a new NULL element in the specified position, making room if the current index already exists.
◉ REMOVE : Removed the specified key from an object, element in an array.
◉ RENAME_KEY : As the name suggests, it renames the specified key with the new value in an object.
◉ APPEND : Append a new element to the end of an array.
The use of these methods will become clearer when you examine the examples below.
◉ Example 1: Amending a JSON Object
The following example creates a new JSON object, then amends it several times using the PUT, PUT_NULL, RENAME_KEY and REMOVE methods.
SET SERVEROUT ON FORMAT WRAPPED LINESIZE 1000
DECLARE
l_obj JSON_OBJECT_T;
PROCEDURE display (p_obj IN JSON_OBJECT_T) IS
BEGIN
DBMS_OUTPUT.put_line(p_obj.stringify);
END;
BEGIN
-- Create a new object.
l_obj := JSON_OBJECT_T('{"employee_no":9999}');
display(l_obj);
-- Add a new element to the object.
l_obj.put('a_string_value', 'A String');
display(l_obj);
-- Amend an existing element in the object.
l_obj.put('a_string_value', 'A New String');
display(l_obj);
-- Rename the key of an existing element in the object.
l_obj.rename_key('a_string_value', 'a_renamed_string_value');
display(l_obj);
-- Remove an element.
l_obj.remove('a_renamed_string_value');
display(l_obj);
-- Add a new object element.
l_obj.put('an_object_value', JSON_OBJECT_T('{"an_object":9999}'));
display(l_obj);
-- Remove an element.
l_obj.remove('an_object_value');
display(l_obj);
-- Add a new array element.
l_obj.put('an_array_value', JSON_ARRAY_T('["text","in","an","arry"]'));
display(l_obj);
-- Set an existing element to NULL.
l_obj.put_null('an_array_value');
display(l_obj);
END;
/
{"employee_no":9999}
{"employee_no":9999,"a_string_value":"A String"}
{"employee_no":9999,"a_string_value":"A New String"}
{"employee_no":9999,"a_renamed_string_value":"A New String"}
{"employee_no":9999}
{"employee_no":9999,"an_object_value":{"an_object":9999}}
{"employee_no":9999}
{"employee_no":9999,"an_array_value":["text","in","an","arry"]}
{"employee_no":9999,"an_array_value":null}
PL/SQL procedure successfully completed.
SQL>
◉ Example 2: Amending a JSON Array
The following example creates a new JSON array, then amends it several times using the APPEND, APPEND_NULL, PUT, PUT_NULL and REMOVE methods.
SET SERVEROUT ON FORMAT WRAPPED LINESIZE 1000
DECLARE
l_arr JSON_ARRAY_T;
PROCEDURE display (p_arr IN JSON_ARRAY_T) IS
BEGIN
DBMS_OUTPUT.put_line(p_arr.stringify);
END;
BEGIN
-- Create a new array.
l_arr := JSON_ARRAY_T('["apple","orange","banana"]');
display(l_arr);
-- Append a new element to the array.
l_arr.append('pear');
display(l_arr);
-- Append a NULL element to the array.
l_arr.append_null;
display(l_arr);
-- Add a new element to the array.
-- 0-based, so 3 is position 4.
l_arr.put(3, 'grape');
display(l_arr);
-- Add a NULL element to the array.
-- 0-based, so 3 is position 4.
l_arr.put_null(3);
display(l_arr);
-- Remove elements from the array.
-- Notice reverse order, as positions are automatically shuffled.
l_arr.remove(6);
l_arr.remove(3);
display(l_arr);
-- Amend an element in the array.
l_arr.remove(3);
l_arr.put(3, 'pineapple');
display(l_arr);
-- Append a new object to the array.
l_arr.append(JSON_OBJECT_T('{"grape":6}'));
display(l_arr);
-- Append a new array to the array.
l_arr.append(JSON_ARRAY_T('[1,2,3,4,5]'));
display(l_arr);
END;
/
["apple","orange","banana"]
["apple","orange","banana","pear"]
["apple","orange","banana","pear",null]
["apple","orange","banana","grape","pear",null]
["apple","orange","banana",null,"grape","pear",null]
["apple","orange","banana","grape","pear"]
["apple","orange","banana","pineapple","pear"]
["apple","orange","banana","pineapple","pear",{"grape":6}]
["apple","orange","banana","pineapple","pear",{"grape":6},[1,2,3,4,5]]
PL/SQL procedure successfully completed.
SQL>
In this second JSON array example, we amend the values in the nested JSON array.
SET SERVEROUTPUT ON FORMAT WRAPPED LINESIZE 1000
DECLARE
l_obj JSON_OBJECT_T;
l_arr JSON_ARRAY_T;
PROCEDURE display (p_obj IN JSON_OBJECT_T) IS
BEGIN
DBMS_OUTPUT.put_line(p_obj.stringify);
END;
PROCEDURE display (p_arr IN JSON_ARRAY_T) IS
BEGIN
DBMS_OUTPUT.put_line(p_arr.stringify);
END;
BEGIN
-- Create a new object containing an array.
l_obj := JSON_OBJECT_T('{"order":1234,"order_items":[{"1":"apple"},{"2":"orange"},{"3":"banana"}]}');
display(l_obj);
-- Get the array out of the object.
l_arr := l_obj.get_Array('order_items');
display(l_arr);
-- Amend an element in the array.
l_arr.put(2, JSON_ELEMENT_T.parse('{"3":"green banana"}'), TRUE);
display(l_arr);
-- Append a new element into the array.
l_arr.append(JSON_ELEMENT_T.parse('{"4":"pineapple"}'));
display(l_arr);
-- Overwrite the array in the object.
l_obj.put('order_items',l_arr);
display(l_obj);
END;
/
{"order":1234,"order_items":[{"1":"apple"},{"2":"orange"},{"3":"banana"}]}
[{"1":"apple"},{"2":"orange"},{"3":"banana"}]
[{"1":"apple"},{"2":"orange"},{"3":"green banana"}]
[{"1":"apple"},{"2":"orange"},{"3":"green banana"},{"4":"pineapple"}]
{"order":1234,"order_items":[{"1":"apple"},{"2":"orange"},{"3":"green banana"},{"4":"pineapple"}]}
PL/SQL procedure successfully completed.
SQL>
◉ Example 3 : Parse JSON Data 1
The examples in the remainder of this article require the following table.
CREATE TABLE json_documents (
id NUMBER,
data CLOB,
CONSTRAINT json_documents_pk PRIMARY KEY (id),
CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);
INSERT INTO json_documents (id, data)
VALUES (1, '{
"departments":[
{
"department":{
"department_name":"DEV",
"department_no":70,
"employees":[
{
"employee_number":9000,
"employee_name":"JONES",
"salary":1000
},
{
"employee_number":9001,
"employee_name":"SMITH",
"salary":2000
}
]
}
},
{
"department":{
"department_name":"DBA",
"department_no":80,
"employees":[
{
"employee_number":9002,
"employee_name":"HALL",
"salary":3000
}
]
}
}
]
}');
COMMIT;
In the following example we know the basic structure of the JSON data, so we loop through the departments array and nested employees array, explicitly requesting the keys of interest to display the data.
SET SERVEROUT ON FORMAT WRAPPED LINESIZE 1000
DECLARE
l_clob CLOB;
l_top_obj JSON_OBJECT_T;
l_dept_arr JSON_ARRAY_T;
l_dept_obj JSON_OBJECT_T;
l_emp_arr JSON_ARRAY_T;
l_emp_obj JSON_OBJECT_T;
BEGIN
SELECT data
INTO l_clob
FROM json_documents
WHERE id = 1;
l_top_obj := JSON_OBJECT_T(l_clob);
l_dept_arr := l_top_obj.get_array('departments');
FOR i IN 0 .. l_dept_arr.get_size - 1 LOOP
DBMS_OUTPUT.put_line('-------------------------------------------');
l_dept_obj := TREAT(l_dept_arr.get(i) AS JSON_OBJECT_T).get_object('department');
DBMS_OUTPUT.put_line('index : ' || i);
DBMS_OUTPUT.put_line('department_name : ' || l_dept_obj.get_string('department_name'));
DBMS_OUTPUT.put_line('department_no : ' || l_dept_obj.get_number('department_no'));
l_emp_arr := l_dept_obj.get_array('employees');
FOR j IN 0 .. l_emp_arr.get_size - 1 LOOP
l_emp_obj := TREAT(l_emp_arr.get(j) AS JSON_OBJECT_T);
DBMS_OUTPUT.put_line(' --');
DBMS_OUTPUT.put_line(' index : ' || j);
DBMS_OUTPUT.put_line(' employee_number : ' || l_emp_obj.get_number('employee_number'));
DBMS_OUTPUT.put_line(' employee_name : ' || l_emp_obj.get_string('employee_name'));
DBMS_OUTPUT.put_line(' salary : ' || l_emp_obj.get_number('salary'));
END LOOP;
END LOOP;
END;
/
-------------------------------------------
index : 0
department_name : DEV
department_no : 70
--
index : 0
employee_number : 9000
employee_name : JONES
salary : 1000
--
index : 1
employee_number : 9001
employee_name : SMITH
salary : 2000
-------------------------------------------
index : 1
department_name : DBA
department_no : 80
--
index : 0
employee_number : 9002
employee_name : HALL
salary : 3000
PL/SQL procedure successfully completed.
SQL>
◉ Example 4 : Parse JSON Data 2
In the following example, we understand the basic structure of the JSON data, an array of departments, with each department containing an array of employees, but we are not sure what keys are present in each department and employee, so we can't request them by name. Instead, we loop through the key list and use introspection to decide how to process the values associated with the keys.
SET SERVEROUT ON FORMAT WRAPPED LINESIZE 1000
DECLARE
l_clob CLOB;
l_top_obj JSON_OBJECT_T;
l_dept_arr JSON_ARRAY_T;
l_dept_obj JSON_OBJECT_T;
l_dept_key_list JSON_KEY_LIST;
l_emp_arr JSON_ARRAY_T;
l_emp_obj JSON_OBJECT_T;
l_emp_key_list JSON_KEY_LIST;
BEGIN
SELECT data
INTO l_clob
FROM json_documents
WHERE id = 1;
l_top_obj := JSON_OBJECT_T(l_clob);
l_dept_arr := l_top_obj.get_array('departments');
<< departments_loop >>
FOR i IN 0 .. l_dept_arr.get_size - 1 LOOP
DBMS_OUTPUT.put_line('-------------------------------------------');
DBMS_OUTPUT.put_line('index : ' || i);
l_dept_obj := TREAT(l_dept_arr.get(i) AS JSON_OBJECT_T).get_object('department');
l_dept_key_list := l_dept_obj.get_keys;
<< department_keys_loop >>
FOR j IN 1 .. l_dept_key_list.count LOOP
IF l_dept_obj.get(l_dept_key_list(j)).is_array THEN
-- This element contains an array, so we must process the array.
l_emp_arr := TREAT(l_dept_obj.get(l_dept_key_list(j)) AS JSON_ARRAY_T);
<< employees_loop >>
FOR j IN 0 .. l_emp_arr.get_size - 1 LOOP
DBMS_OUTPUT.put_line(' --');
DBMS_OUTPUT.put_line(' index : ' || j);
IF l_emp_arr.get(j).is_object THEN
l_emp_obj := TREAT(l_emp_arr.get(j) AS JSON_OBJECT_T);
l_emp_key_list := l_emp_obj.get_keys;
<< employee_keys_loop >>
FOR k IN 1 .. l_emp_key_list.count LOOP
DBMS_OUTPUT.put_line(' ' || l_emp_key_list(k) || ' : ' || l_emp_obj.get_string(l_emp_key_list(k)));
END LOOP;
NULL;
ELSE
DBMS_OUTPUT.put_line('We were expecting an employee to be an object!');
END IF;
END LOOP;
ELSIF l_dept_obj.get(l_dept_key_list(j)).is_number THEN
-- It is a number.
DBMS_OUTPUT.put_line(l_dept_key_list(j) || ' : ' || l_dept_obj.get_number(l_dept_key_list(j)));
ELSIF l_dept_obj.get(l_dept_key_list(j)).is_string THEN
-- It is a number.
DBMS_OUTPUT.put_line(l_dept_key_list(j) || ' : ' || l_dept_obj.get_string(l_dept_key_list(j)));
ELSE
-- Catch-all. Stringify.
DBMS_OUTPUT.put_line(l_dept_key_list(j) || ' : ' || l_dept_obj.get(l_dept_key_list(j)).stringify);
END IF;
END LOOP;
END LOOP;
END;
/
-------------------------------------------
index : 0
department_name : DEV
department_no : 70
--
index : 0
employee_number : 9000
employee_name : JONES
salary : 1000
--
index : 1
employee_number : 9001
employee_name : SMITH
salary : 2000
-------------------------------------------
index : 1
department_name : DBA
department_no : 80
--
index : 0
employee_number : 9002
employee_name : HALL
salary : 3000
PL/SQL procedure successfully completed.
SQL>
Posts
0 comments:
Post a Comment