To facilitate manipulation of JSON data, OceanBase Database supports the following JSON data types in PL statements: JSON_OBJECT_T and JSON_ELEMENT_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;
/