To facilitate manipulation of JSON data, OceanBase Database supports the following JSON data types in PL statements: JSON_OBJECT_T, JSON_ELEMENT_T, and JSON_ARRAY_T.
The static function parse takes a VARCHAR2, CLOB, or BLOB instance as an input, and parses the input as JSON text to return a JSON_ELEMENT_T or JSON_OBJECT_T instance.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
JSON_ELEMENT_T object type
For JSON_ELEMENT_T, JSON text is parsed to create a JSON_ELEMENT_T instance.
Create a JSON_ELEMENT_T instance
You can use the parse function to create a JSON_ELEMENT_T instance. The parse function parses a VARCHAR2, CLOB, or BLOB input as a JSON string to return a JSON_ELEMENT_T instance. If the input is not valid JSON data, an error is returned. Therefore, you can specify the IS JSON option in the SQL statement as an input constraint.
STATIC FUNCTION parse(json VARCHAR2) RETURN JSON_ELEMENT_T
STATIC FUNCTION parse(json CLOB) RETURN JSON_ELEMENT_T
STATIC FUNCTION parse(json BLOB) RETURN JSON_ELEMENT_T
You cannot create an empty JSON_ELEMENT_T instance. You can create an empty JSON container based on one of the subtypes.
Serialize a JSON object
Serialization is the inverse of the parse function. During the serialization, the input JSON data is serialized to return a string. The name of a serialization method begins with to_. Most serialization methods are member functions. However, the serialization method for CLOB and BLOB instances can be a member function or member procedure.
MEMBER FUNCTION to_String RETURN VARCHAR2
MEMBER FUNCTION to_Number RETURN NUMBER
MEMBER FUNCTION to_Date RETURN DATE
MEMBER FUNCTION to_Timestamp RETURN TIMESTAMP
MEMBER FUNCTION to_Boolean RETURN BOOLEAN
MEMBER FUNCTION to_Clob RETURN CLOB
MEMBER FUNCTION to_Blob RETURN BLOB
MEMBER PROCEDURE to_Clob(c IN OUT CLOB)
MEMBER PROCEDURE to_Blob(c IN OUT BLOB)
Query the attributes of a JSON object
You can use the following functions to query the attributes of a JSON object without modifying the JSON object:
MEMBER FUNCTION is_Object RETURN BOOLEAN
MEMBER FUNCTION is_Array RETURN BOOLEAN
MEMBER FUNCTION is_Scalar RETURN BOOLEAN
MEMBER FUNCTION is_String RETURN BOOLEAN
MEMBER FUNCTION is_Number RETURN BOOLEAN
MEMBER FUNCTION is_Boolean RETURN BOOLEAN
MEMBER FUNCTION is_True RETURN BOOLEAN
MEMBER FUNCTION is_False RETURN BOOLEAN
MEMBER FUNCTION is_Null RETURN BOOLEAN
MEMBER FUNCTION is_Date RETURN BOOLEAN
MEMBER FUNCTION is_Timestamp RETURN BOOLEAN
MEMBER FUNCTION get_Size RETURN NUMBER
The return value of the get_Size function depends on the JSON type:
- For a scalar, the function returns
1. - For an object, the function returns the number of keys.
- For an array, the function returns the number of elements.
JSON_OBJECT_T object type
The subtype JSON_OBJECT_T corresponds to the JSON object structure and provides a constructor, which is also called JSON_OBJECT_T, for you to construct an empty JSON object. You can then add object members to the empty object as needed.
Construct a JSON object
You can use the following constructor to construct an empty JSON_OBJECT_T object:
CONSTRUCTOR FUNCTION JSON_OBJECT_T RETURN SELF AS RESULT
You can use the parse function to create a JSON_OBJECT_T instance. This function uses a JSON string as the input. If the input is not valid JSON data, an error is returned.
STATIC FUNCTION parse(json VARCHAR2) RETURN JSON_OBJECT_T
STATIC FUNCTION parse(json CLOB) RETURN JSON_OBJECT_T
STATIC FUNCTION parse(json BLOB) RETURN JSON_OBJECT_T
You can also use one of the following constructors to create a JSON_OBJECT_T instance:
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn VARCHAR2) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn CLOB) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn BLOB) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION JSON_OBJECT_T(e JSON_ELEMENT_T) RETURN SELF AS RESULT
Retrieve the value of a JSON object
The following functions and procedures allow you to retrieve the value of a JSON object:
get(key VARCHAR2) RETURN JSON_ELEMENT_T // Return the retrieved JSON_ELEMENT_T value together with the base class of the JSON object or not, which depends on certain conditions.
get_String(key VARCHAR2) RETURN VARCHAR2 // Return the retrieved value as a VARCHAR2 value.
get_Number(key VARCHAR2) RETURN NUMBER // Return the retrieved value as a NUMBER value.
get_Date(key VARCHAR2) RETURN DATE // Return the retrieved value as a DATE value.
get_Timestamp(key VARCHAR2) RETURN TIMESTAMP // Return the retrieved value as a TIMESTAMP value.
get_Boolean(key VARCHAR2) RETURN BOOLEAN // Return the retrieved value as a BOOLEAN value.
get_Clob(key VARCHAR2) RETURN CLOB // Return the retrieved value as a CLOB value.
get_Blob(key VARCHAR2) RETURN BLOB // Return the retrieved value as a BLOB value.
get_Object(key VARCHAR2) RETURN JSON_OBJECT_T // Return the retrieved value as a JSON_OBJECT_T value.
get_Array(key VARCHAR2) RETURN JSON_ARRAY_T // Return the retrieved value as a JSON_ARRAY_T value.
Set the value of a JSON object
The following procedures allow you to set the value of a JSON object. If the JSON object already has a value, the existing value is overwritten.
PROCEDURE get_Clob(key NUMBER, c IN OUT CLOB) // Convert the retrieved value to a CLOB value and then assign it to the c parameter.
PROCEDURE get_Blob(key NUMBER, c IN OUT BLOB) // Convert the retrieved value to a BLOB value and then assign it to the c parameter.
PROCEDURE put(key VARCHAR2, value JSON_ELEMENT_T) // Add a key-value pair where the value is of the JSON_ELEMENT_T data type, and set the base class or not depending on the conditions.
PROCEDURE put(key VARCHAR2, value VARCHAR2) // Add a key-value pair where the value is of the VARCHAR2 data type.
PROCEDURE put(key VARCHAR2, value NUMBER) // Add a key-value pair where the value is of the NUMBER data type.
PROCEDURE put(key VARCHAR2, value BOOLEAN) // Add a key-value pair where the value is of the BOOLEAN data type.
PROCEDURE put(key VARCHAR2, value DATE) // Add a key-value pair where the value is of the DATE data type.
PROCEDURE put(key VARCHAR2, value TIMESTAMP) // Add a key-value pair where the value is of the TIMESTAMP data type.
PROCEDURE put_Null(key VARCHAR2) // Add a key-value pair where the value is null.
Query the attributes of a JSON object
You can use the following functions to query the attributes of a JSON object without modifying the JSON object:
has(key VARCHAR2) RETURN BOOLEAN // Indicates whether the key exists. This function is equivalent to json_exist(json_doc, "$.key").
get_Type(key VARCHAR2) RETURN VARCHAR2 // This function is equivalent to json_query(json_doc, "$.key.type()").
The following example shows how to use the JSON_OBJECT_T object type:
# For parse
declare
jo JSON_OBJECT_T;
jo_val BLOB;
begin
jo := JSON_OBJECT_T.parse('{"a":"xyz"}');
jo_val := jo.get_Blob('a');
dbms_output.put_line(utl_raw.cast_to_varchar2(jo_val));
end;
/
# For parse
declare
jo JSON_OBJECT_T;
lob CLOB := EMPTY_CLOB();
begin
jo := JSON_OBJECT_T.parse('{"a":"xyz"}');
jo.get_Clob('a', lob);
dbms_output.put_line(lob);
end;
/
# For put/get
declare
jo JSON_OBJECT_T;
je JSON_ELEMENT_T;
begin
jo := JSON_OBJECT_T;
jo.put('a', false);
je := jo.get('a');
if (je.is_False) THEN
dbms_output.put_line('is_False');
END IF;
end;
/
# For put/get
declare
jo JSON_OBJECT_T;
je JSON_ELEMENT_T;
jo_val TIMESTAMP;
begin
jo := JSON_OBJECT_T;
jo_val := '2023-01-02 10:10:32.000000';
jo.put('a', jo_val);
je := jo.get('a');
if (je.is_Timestamp) THEN
dbms_output.put_line('is_Timestamp');
END IF;
end;
/
# For put/get/to_String/parse
declare
jo_src JSON_OBJECT_T;
jo_dst JSON_OBJECT_T;
begin
jo_src := JSON_OBJECT_T.parse('{"a":"xyz"}');
jo_dst := jo_src.clone;
jo_src.put('b', 'mnl');
jo_dst.put('c', 'hik');
dbms_output.put_line(jo_src.to_String);
dbms_output.put_line(jo_dst.to_String);
end;
/
JSON_ARRAY_T object type
JSON_ARRAY_T is a subtype of JSON_ELEMENT_T and is used to represent the array type in JSON.
The JSON_ARRAY_T object type provides three types of functions: static functions, member functions, and constructor functions.
Usage
JSON_ARRAY_Tprovides five constructor functions for you to conveniently createJSON_ARRAY_Tobjects. The constructor functions support different parameter types, including CLOB, VARCHAR2, and other JSON element types.CONSTRUCTOR FUNCTION JSON_ARRAY_T RETURN SELF AS RESULT CONSTRUCTOR FUNCTION JSON_ARRAY_T(o JSON_ELEMENT_T) RETURN SELF AS RESULT CONSTRUCTOR FUNCTION JSON_ARRAY_T(o JSON_ARRAY_T) RETURN SELF AS RESULT CONSTRUCTOR FUNCTION JSON_ARRAY_T(jsn VARCHAR2) RETURN SELF AS RESULT CONSTRUCTOR FUNCTION JSON_ARRAY_T(jsn CLOB) RETURN SELF AS RESULTIn a JSON array of the
JSON_ARRAY_Ttype, each element corresponds to a unique subscript. You can use thegetmethod to obtain an element at a specific position based on the subscripts of the array. This way, you can traverse and access all elements in the JSON array.MEMBER FUNCTION get(pos NUMBER) RETURN JSON_ELEMENT_TJSON_ARRAY_Tprovides theget_size()andget_Type(pos NUMBER)introspection methods. You can use theget_size()method to obtain the number of elements in an array, and theget_Type(pos NUMBER)method to obtain the data type of an element at the specified position.MEMBER FUNCTION get_Type(pos NUMBER) RETURN VARCHAR2 MEMBER FUNCTION get_size RETURN NUMBER
The following examples illustrate how to use the JSON_ARRAY_T data type.
Create a JSON_ARRAY_T object
The following example creates a JSON_ARRAY_T object by using a constructor function.
DECLARE
jo JSON_ARRAY_T;
BEGIN
jo := JSON_ARRAY_T('[123]');
DBMS_OUTPUT.PUT_LINE(jo.to_String);
END;
/
Obtain elements by using subscripts
The following SQL code takes parsing a JSON string as an example to show how to use the get method to obtain elements in an array based on subscripts.
DECLARE
jo JSON_ARRAY_T;
jo_val JSON_ELEMENT_T;
BEGIN
jo := JSON_ARRAY_T.parse('[123,{"abc":456},[789], true, null,"test"]');
FOR I IN 0 .. 5 LOOP
jo_val := jo.get(i);
DBMS_OUTPUT.PUT_LINE(jo_val.to_String);
END LOOP;
END;
/
Count the number of elements in an array
The following SQL code takes parsing a JSON string as an example to show how to use the get_size method to count the number of elements in an array and use a loop to traverse the elements.
declare
jo JSON_ARRAY_T;
jo_val JSON_ELEMENT_T;
begin
jo := JSON_ARRAY_T.parse('[123,{"abc":456},[789], true, null,"test"]');
FOR I IN 0 .. jo.get_size-1 LOOP
jo_val := jo.get(i);
dbms_output.put_line(jo_val.to_String);
END LOOP;
end;
/