8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Related articles.

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.

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".

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.

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.

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.

The following methods are used to amend the JSON data.

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>

JSON Data Type Support (21c)

Oracle database 21c introduced a new JSON 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 JSON_OBJECT_T constructor supports the new JSON 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...

Back to the Top.