To facilitate operations on JSON data, OceanBase Database supports three JSON data types in PL statements: JSON_OBJECT_T, JSON_ELEMENT_T, and JSON_ARRAY_T.
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 does not support this feature.
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 a JSON_ELEMENT_T instance. 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 will occur. 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. You can create an empty JSON container based on one of its subtypes.
Serializing JSON object types
Serialization is the reverse process 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. 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 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 // Return the base class type if applicable
get_String(key VARCHAR2) RETURN VARCHAR2 // Convert the found value to VARCHAR2
get_Number(key VARCHAR2) RETURN NUMBER // Convert the found value to NUMBER
get_Date(key VARCHAR2) RETURN DATE // Convert the found value to DATE
get_Timestamp(key VARCHAR2) RETURN TIMESTAMP // Convert the found value to TIMESTAMP
get_Boolean(key VARCHAR2) RETURN BOOLEAN // Convert the found value to BOOLEAN
get_Clob(key VARCHAR2) RETURN CLOB // Convert the found value to CLOB
get_Blob(key VARCHAR2) RETURN BLOB // Convert the found value to BLOB
get_Object(key VARCHAR2) RETURN JSON_OBJECT_T // Convert the found value 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) // Convert the found value to CLOB and place it in the c parameter
PROCEDURE get_Blob(key NUMBER, c IN OUT BLOB) // Convert the found value to BLOB and place it in the c parameter
PROCEDURE put(key VARCHAR2, value JSON_ELEMENT_T) // Add a K-V pair, value type is JSON_ELEMENT_T, set the base class if applicable
PROCEDURE put(key VARCHAR2, value VARCHAR2) // Add a K-V pair, value type is VARCHAR2
PROCEDURE put(key VARCHAR2, value NUMBER) // Add a K-V pair, value type is NUMBER
PROCEDURE put(key VARCHAR2, value BOOLEAN) // Add a K-V pair, value type is BOOLEAN
PROCEDURE put(key VARCHAR2, value DATE) // Add a K-V pair, value type is DATE
PROCEDURE put(key VARCHAR2, value TIMESTAMP) // Add a K-V pair, value type is TIMESTAMP
PROCEDURE put_Null(key VARCHAR2) // Add a K-V pair, value type 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 // Check 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 of JSON data.
JSON_ARRAY_T includes static functions (STATIC FUNCTION), member functions (MEMBER FUNCTION), and constructor functions (CONSTRUCTOR FUNCTION).
Description
JSON_ARRAY_Tprovides five constructors that make it easy to 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 the
JSON_ARRAY_Ttype, each element corresponds to a unique index. You can use thegetmethod to retrieve elements from specific positions in the JSON array using their indexes. This allows for traversal and access to individual elements within the array.
MEMBER FUNCTION get(pos NUMBER) RETURN JSON_ELEMENT_T
- The
get_size()method inJSON_ARRAY_Tis used to get the number of elements in the array. Theget_Type(pos NUMBER)method is used to get the data type of the element at the specified position.
MEMBER FUNCTION get_Type(pos NUMBER) RETURN VARCHAR2
MEMBER FUNCTION get_size RETURN NUMBER
An example of using JSON_ARRAY_T is as follows:
Examples of constructing the JSON_ARRAY_T function
The following example shows how to create a JSON_ARRAY_T object by using a constructor.
DECLARE
jo JSON_ARRAY_T;
BEGIN
jo := JSON_ARRAY_T('[123]');
DBMS_OUTPUT.PUT_LINE(jo.to_String);
END;
/
Example of getting an element by index
The following SQL statement uses the get method and array indexing to extract an element from a JSON 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 .. 5 LOOP
jo_val := jo.get(i);
DBMS_OUTPUT.PUT_LINE(jo_val.to_String);
END LOOP;
END;
/
Count statistics elements
In the following SQL statement, the get_size method is used to count the number of elements in the array. The elements are then traversed in a loop 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
The append function adds one JSON element to the end of the current JSON_ARRAY_T object.
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)
The value specifies a single JSON element that is to be 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('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
The append_Null function is used to append a Null value to the current JSON array, modifying the array object directly. It has no input parameters and returns no 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 type (which can be a JSON array, JSON object, or a single JSON element) to the current JSON_ARRAY_T object (which supports de duplication of the inserted element).
Syntax
MEMBER PROCEDURE append_all(arr JSON_ARRAY_T, excl_exis BOOLEAN DEFAULT FALSE)
Where:
arr JSON_ARRAY_T: This parameter is a JSON array that contains multiple elements.excl_exis: This parameter is a boolean value used to determine whether to exclude existing elements. The default value isFALSE, which means all elements will be added to the target array without checking if they already exist. If set toTRUE, only elements not present in the target array will be appended.
Examples
The default value for excl_exis is FALSE, which means all elements are added to the target array without checking if they already exist:
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 is by default set to false
dbms_output.put_line(je.to_String);
dbms_output.put_line(jo.to_String);
END;
/
SELECT PRINTLN() FROM dual;
/
If excl_exis is set to TRUE, only elements not present in the destination 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;
/
CODE_PLACEHOLDER_c8b7c027eea0471f8ee9cdb7d84fa749