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-compatible mode.
JSON_ELEMENT_T object type
JSON_ELEMENT_T instances are constructed by parsing JSON text.
Creating JSON_ELEMENT_T instances
You can create JSON_ELEMENT_T instances 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 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. However, you can create an empty JSON container based on one of its subtypes.
Serializing JSON object types
Serialization is the reverse of parsing. It takes the input JSON data and returns a string. The names of serialization methods start with the prefix to_. Most serialization methods are member functions. However, for CLOB or BLOB instances, serialization methods can be either member functions or 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 JSON objects
You can use the following functions to query the properties of JSON objects without modifying them.
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. JSON_OBJECT_T 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
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 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
The JSON_ARRAY_T type is the subtype of JSON_ELEMENT_T and is used to represent array types in JSON data.
JSON_ARRAY_T includes static functions (STATIC FUNCTION), member functions (MEMBER FUNCTION), and constructors (CONSTRUCTOR FUNCTION).
How to use
- The
JSON_ARRAY_Ttype provides five constructors for easily creatingJSON_ARRAY_Tobjects. These constructors support parameters of various 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 JSON arrays of type JSON_ARRAY_T, each element corresponds to a unique index. The get method allows users to access specific elements in the array using their 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. Use theget_size()method to get the number of array elements; use theget_type(pos NUMBER)method to get 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 JSON_ARRAY_T type can be used as shown in the following example:
Example of constructing a JSON_ARRAY_T function
The following example creates a JSON_ARRAY_T object by using the constructor.
DECLARE
jo JSON_ARRAY_T;
BEGIN
jo := JSON_ARRAY_T('[123]');
DBMS_OUTPUT.PUT_LINE(jo.to_String);
END;
/
Get an element by using the subscripts
The following SQL example demonstrates how to obtain array elements using the get method with an 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;
/
Example 1: Count the number of statistics
The following SQL example shows how to count array elements by using the get_size method and how to get each element by looping through the array.
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 type
The append method appends a single JSON element to the end of the current JSON_ARRAY_T object and directly modifies 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)
value specifies a JSON element that is appended to the current JSON_ARRAY_T object.
Examples
declare
jo_src JSON_ARRAY_T;
begin
jo_src := JSON_ARRAY_T();
jo_src.append('"OceanBase"');
jo_src.append('OceanBase');
jo_src.append('Marine database');
The `DELETE_SCHEMA_STATS` procedure deletes statistics in the 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
The append_Null procedure appends a Null value to the current JSON array by directly modifying the array object itself. It does not require any input parameters and does not return any value.
Syntax
MEMBER PROCEDURE append_Null
Examples
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
The append_all method appends a JSON element (an array, object, or scalar value) to the current JSON_ARRAY_T object, and optionally removes duplicates from the inserted elements.
Syntax
MEMBER PROCEDURE append_all(arr JSON_ARRAY_T, excl_exis BOOLEAN DEFAULT FALSE)
The following are the rules:
arr JSON_ARRAY_T: This parameter is a JSON array that contains multiple elements.excl_exis: This is a Boolean parameter, which specifies whether to skip elements that exist in the target array. The default value isFALSE, meaning that all elements are added to the target array without checking for existence. When the value is set toTRUE, only elements that do not already exist in the target array are added.
Example
excl_exis is FALSE by default, meaning that all elements are added to the target array without checking for their existence:
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); -- default value of excl_exis is 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, new elements are appended only if they do not exist in the destination array:
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;
/