DBA Hub

📋Steps in this guide1/11

PL/SQL Object Types for JSON in Oracle Database 12c Release 2 (12.2)

Oracle 12c Release 2 (12.2) includes several enhancements to the JSON functionality in the database including native support for JSON parsing and generation in PL/SQL.

oracle 12cconfigurationintermediate
by OracleDba
29 views
1

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 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. - : The supertype some of the other object types extend. You will not often use this type directly. You can cast a to a subtype using . For example, " " - : An object representing a JSON object. - : An object representing a JSON array. - : A scalar value associated with a key such as a string, number, boolean or NULL. - : An array of key names, typically returned by the method. These objects are documented on the JSON Data Structures page of the Database PL/SQL Packages and Types Reference manual. Rather the repeating all that information, this article will just provide some general direction and examples. 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".
2

Constructors

You will typically create new instances of the object types in one of three ways.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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>
3

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 and functions do the same thing, but the former will be more familiar to JavaScript developers.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
4

Introspection Methods

The JSON object types include type-introspection member functions that can be used to identify their contents. With the exception of the method they should all be self explanatory. Depending on the object type, the method returns the following. - : Returns 1 - : Returns the number of top-level keys. - : Returns the number of array elements.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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
5

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 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. Each object can have its error handling set separately.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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>
6

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. - : Returns the JSON_ELEMENT_T associated with the specified key. - : Returns the JSON_OBJECT_T associated with the specified key. - : Returns the JSON_ARRAY_T associated with the specified key. - : 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 without a key. There is a member function and procedure for and . - : Creates a full copy of the object specified by the key. The following methods are used to amend the JSON data. - : 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. - : 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. - : Removed the specified key from an object, element in an array. - : As the name suggests, it renames the specified key with the new value in an object. - : Append a new element to the end of an array. The use of these methods will become clearer when you examine the examples below.
7

Example 1: Amending a JSON Object

The following example creates a new JSON object, then amends it several times using the , , and methods.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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>
8

Example 2: Amending a JSON Array

The following example creates a new JSON array, then amends it several times using the , , , and methods. In this second JSON array example, we amend the values in the nested JSON array.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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>

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>
9

Example 3 : Parse JSON Data 1

The examples in the remainder of this article require the following table. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
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;

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>
10

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
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>
11

JSON Data Type Support (21c)

Oracle database 21c introduced a new data type to provide native JSON support and improve the performance of JSON processing. PL/SQL Object Type for JSON have been updated to allow interaction with the new data type. The constructor supports the new data type, and there are a number of new member functions, as well as overloads of existing member functions that support the new data type. You can read more about it here . For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!