To facilitate operations on JSON data, OceanBase Database supports the JSON_OBJECT_T, JSON_ELEMENT_T, and JSON_ARRAY_T data types in PL statements.
The static parse function takes an instance of VARCHAR2, CLOB, or BLOB as an input parameter and parses it into JSON text, returning an instance of JSON_ELEMENT_T or JSON_OBJECT_T.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
JSON_ELEMENT_T object type
JSON_ELEMENT_T instances are constructed by parsing JSON text.
Create a JSON_ELEMENT_T instance
You can create a JSON_ELEMENT_T instance by using the parse function. When you use the parse function to create a JSON_ELEMENT_T instance, the function takes VARCHAR2, CLOB, or BLOB data as a JSON string and returns a JSON_ELEMENT_T instance. If the input is not valid JSON, an error occurs. You can use the IS JSON SQL condition to constrain the input.
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 its subtypes.
Serialization of JSON object types
Serialization is the inverse of the parse function. It parses the input JSON data and returns a string. The names of serialization methods start with the to_ prefix. Most serialization methods are member functions. However, for the serialization of CLOB or BLOB instances, there are both member functions and member procedures.
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 properties of a JSON object
You can use the following functions to query the properties of a JSON object without modifying it.
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 scalars, it returns 1.
- For objects, it returns the number of keys.
- For arrays, it returns the number of elements.
JSON_OBJECT_T object type
The JSON_OBJECT_T subtype is used for JSON objects, and it has a constructor with the same name that can be used to create an empty JSON object, which can then be populated with members as needed.
Construct a JSON object
Use the following constructor to create an empty JSON_OBJECT_T object:
CONSTRUCTOR FUNCTION JSON_OBJECT_T RETURN SELF AS RESULT
Use the following parse function to create a JSON_OBJECT_T instance. This function takes a JSON string as input and will throw an error if the input is not valid JSON.
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 the following function 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 values from a JSON object
Use the following functions and procedures to retrieve values from a JSON object.
get(key VARCHAR2) RETURN JSON_ELEMENT_T // Returns the base class type if applicable
get_String(key VARCHAR2) RETURN VARCHAR2 // Returns the found value converted to VARCHAR2
get_Number(key VARCHAR2) RETURN NUMBER // Returns the found value converted to NUMBER
get_Date(key VARCHAR2) RETURN DATE // Returns the found value converted to DATE
get_Timestamp(key VARCHAR2) RETURN TIMESTAMP // Returns the found value converted to TIMESTAMP
get_Boolean(key VARCHAR2) RETURN BOOLEAN // Returns the found value converted to BOOLEAN
get_Clob(key VARCHAR2) RETURN CLOB // Returns the found value converted to CLOB
get_Blob(key VARCHAR2) RETURN BLOB // Returns the found value converted to BLOB
get_Object(key VARCHAR2) RETURN JSON_OBJECT_T // Returns the found value converted to JSON_OBJECT_T
get_Array(key VARCHAR2) RETURN JSON_ARRAY_T // Returns the found value converted to JSON_ARRAY_T
Set values in a JSON object
Use the following procedures to set values in a JSON object. If a value already exists, it will be overwritten.
PROCEDURE get_Clob(key NUMBER, c IN OUT CLOB) // Converts the found value to CLOB and places it in the c parameter
PROCEDURE get_Blob(key NUMBER, c IN OUT BLOB) // Converts the found value to BLOB and places it in the c parameter
PROCEDURE put(key VARCHAR2, value JSON_ELEMENT_T) // Adds a K-V pair where the value is of type JSON_ELEMENT_T
PROCEDURE put(key VARCHAR2, value VARCHAR2) // Adds a K-V pair where the value is of type VARCHAR2
PROCEDURE put(key VARCHAR2, value NUMBER) // Adds a K-V pair where the value is of type NUMBER
PROCEDURE put(key VARCHAR2, value BOOLEAN) // Adds a K-V pair where the value is of type BOOLEAN
PROCEDURE put(key VARCHAR2, value DATE) // Adds a K-V pair where the value is of type DATE
PROCEDURE put(key VARCHAR2, value TIMESTAMP) // Adds a K-V pair where the value is of type TIMESTAMP
PROCEDURE put_Null(key VARCHAR2) // Adds a K-V pair where the value is null
Query properties of a JSON object
Use the following functions to query properties of a JSON object without modifying it.
has(key VARCHAR2) RETURN BOOLEAN // Checks if the key exists, equivalent to json_exist(json_doc, "$.key")
get_Type(key VARCHAR2) RETURN VARCHAR2 // Equivalent to json_query(json_doc, "$.key.type()")
Here is an example of using JSON_OBJECT_T:
# 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;
/
# 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;
/
# put/get methods
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;
/
# put/get methods
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;
/
# put/get/to_string/parse usage
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 used to represent the Array type in JSON data.
JSON_ARRAY_T primarily includes static functions (STATIC FUNCTION), member functions (MEMBER FUNCTION), and constructor functions (CONSTRUCTOR FUNCTION).
Usage
JSON_ARRAY_Tprovides five constructors, allowing users to easily createJSON_ARRAY_Tobjects. These constructors support various 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 RESULT
- In a JSON array of type
JSON_ARRAY_T, each element corresponds to a unique index. Thegetmethod allows users to retrieve elements at specific positions using array indices, facilitating traversal and access to elements within the JSON array.
MEMBER FUNCTION get(pos NUMBER) RETURN JSON_ELEMENT_T
JSON_ARRAY_Tprovides theget_size()andget_Type(pos NUMBER)introspection methods. Theget_size()method is used to obtain the number of array elements, while theget_Type(pos NUMBER)method is used to obtain the data type of the element at the specified position.
MEMBER FUNCTION get_Type(pos NUMBER) RETURN VARCHAR2
MEMBER FUNCTION get_size RETURN NUMBER
Here is an example of using JSON_ARRAY_T:
Constructing a JSON_ARRAY_T object
The following example demonstrates how to create a JSON_ARRAY_T object using a constructor.
DECLARE
jo JSON_ARRAY_T;
BEGIN
jo := JSON_ARRAY_T('[123]');
DBMS_OUTPUT.PUT_LINE(jo.to_String);
END;
/
Retrieving elements by index
The following SQL example demonstrates how to use the get method to retrieve array elements by index when parsing a JSON string.
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;
/
Counting the number of elements
The following SQL example demonstrates how to use the get_size method to count the number of elements in an array and how to traverse and retrieve each element using a loop.
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;
/