To facilitate operations on JSON data, OceanBase Database supports the JSON_OBJECT_T, JSON_ELEMENT_T, and JSON_ARRAY_T JSON data types in PL statements.
The static parse function takes a VARCHAR2, CLOB, or BLOB instance as an input parameter, parses it into JSON text, and returns an instance of type 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.
Creating JSON_ELEMENT_T instances
You can use the parse function to create JSON_ELEMENT_T instances. 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 input 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 as input constraints.
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.
Serializing JSON object types
Serialization is the reverse process of parsing, where the input JSON data is parsed and a string is returned. The names of serialization methods start with the prefix to_. Most serialization methods are member functions. However, for serializing 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)
Querying 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. The JSON_OBJECT_T type has a constructor with the same name, which can be used to create an empty JSON object, and then members can be added 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 raise 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 as a VARCHAR2
get_Number(key VARCHAR2) RETURN NUMBER // Returns the found value as a NUMBER
get_Date(key VARCHAR2) RETURN DATE // Returns the found value as a DATE
get_Timestamp(key VARCHAR2) RETURN TIMESTAMP // Returns the found value as a TIMESTAMP
get_Boolean(key VARCHAR2) RETURN BOOLEAN // Returns the found value as a BOOLEAN
get_Clob(key VARCHAR2) RETURN CLOB // Returns the found value as a CLOB
get_Blob(key VARCHAR2) RETURN BLOB // Returns the found value as a BLOB
get_Object(key VARCHAR2) RETURN JSON_OBJECT_T // Returns the found value as a JSON_OBJECT_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 a CLOB and places it in the c parameter
PROCEDURE get_Blob(key NUMBER, c IN OUT BLOB) // Converts the found value to a 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 are some examples 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 array types in JSON data.
JSON_ARRAY_T mainly includes static functions (STATIC FUNCTION), member functions (MEMBER FUNCTION), and constructor functions (CONSTRUCTOR FUNCTION).
Usage
JSON_ARRAY_Tprovides 5 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_TJSON array, each element corresponds to a unique index. Thegetmethod allows users to retrieve elements by their array index, facilitating traversal and access to individual 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 elements from a JSON array by their index.
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 a JSON array and how to traverse the array to retrieve each element.
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;
/
JSON_ARRAY_APPEND object type
append is used to add a single JSON element to the end of the current JSON_ARRAY_T object, directly modifying the array object itself.
Syntax
MEMBER PROCEDURE append(value JSON_ELEMENT_T)
MEMBER PROCEDURE append(value VARCHAR2)
MEMBER PROCEDURE append(value NUMBER)
MEMBER PROCEDURE append(value BOOLEAN)
MEMBER PROCEDURE append(value DATE)
MEMBER PROCEDURE append(value TIMESTAMP)
MEMBER PROCEDURE append(value BLOB)
MEMBER PROCEDURE append(value CLOB)
MEMBER PROCEDURE append(value JSON)
Here, value refers to the single JSON element to be appended to the current JSON_ARRAY_T object.
Example
declare
jo_src JSON_ARRAY_T;
begin
jo_src := JSON_ARRAY_T();
jo_src.append('"OceanBase"');
jo_src.append('OceanBase');
jo_src.append('OceanBase Database');
jo_src.append(JSON_ARRAY_T('[1,2,3]'));
jo_src.append(JSON_OBJECT_T('{"David Tao" : 1}'));
jo_src.append(true);
jo_src.append(1);
jo_src.append(1.1);
jo_src.append(-4294967296);
jo_src.append(18446744073709551616);
dbms_output.put_line(jo_src.to_String);
end;
/
JSON_ARRAY_APPEND_NULL object type
append_Null is used to add a Null value to the current JSON array, directly modifying the array object itself. It has no input parameters and no return value.
Syntax
MEMBER PROCEDURE append_Null
Example
DECLARE
jo JSON_ARRAY_T;
BEGIN
jo := JSON_ARRAY_T;
jo.append_Null;
jo.append_Null;
dbms_output.put_line(jo.to_String);
END;
/
SELECT println() FROM dual;
/
JSON_ARRAY_APPEND_ALL object type
append_all appends a JSON element (which can be a JSON array, JSON object, or a single JSON element) to the current JSON_ARRAY_T object. It also supports deduplication of inserted elements.
Syntax
MEMBER PROCEDURE append_all(arr JSON_ARRAY_T, excl_exis BOOLEAN DEFAULT FALSE)
Here:
arr JSON_ARRAY_T: This parameter is a JSON array containing multiple sub-elements.excl_exis: This is a boolean parameter that determines whether to exclude existing elements. The default value isFALSE, indicating that all elements are added to the target array without checking for duplicates. If set toTRUE, only elements not already present in the target array are appended.
Example
By default, excl_exis is set to FALSE, meaning all elements are added to the target array without checking for duplicates:
DECLARE
jo JSON_ARRAY_T;
je JSON_ELEMENT_T;
js json_object_t:=json_object_t('{"key": "OceanBase"}');
BEGIN
jo := JSON_ARRAY_T('[1,2,"OceanBase"]');
dbms_output.put_line(jo.to_String);
je :=js.get('key');
jo.append_all(je); -- excl_exis defaults to false
dbms_output.put_line(je.to_String);
dbms_output.put_line(jo.to_String);
END;
/
SELECT PRINTLN() FROM dual;
/
When excl_exis is set to TRUE, only elements not already present in the target array are appended:
DECLARE
jo JSON_ARRAY_T;
je JSON_ELEMENT_T;
js json_object_t:=json_object_t('{"key": "OceanBase"}');
BEGIN
jo := JSON_ARRAY_T('[1,2,"OceanBase"]');
dbms_output.put_line(jo.to_String);
je :=js.get('key');
jo.append_all(je, true);
dbms_output.put_line(je.to_String);
dbms_output.put_line(jo.to_String);
END;
/