JSON composite types

Parse a JSON string

STATIC FUNCTION parse(jsn VARCHAR2) RETURN JSON_ELEMENT_T
STATIC FUNCTION parse(jsn CLOB)    RETURN JSON_ELEMENT_T

Example

DECLARE
    element JSON_ELEMENT_T;
BEGIN
    element := JSON_ELEMENT_T.Parse('{"name": "Alice", "age": 30}');
END;
/

Serialize to string

MEMBER FUNCTION stringify(self IN JSON_ELEMENT_T) RETURN VARCHAR2
MEMBER FUNCTION to_String(self IN JSON_ELEMENT_T) RETURN VARCHAR2
MEMBER FUNCTION to_Clob(self IN JSON_ELEMENT_T)   RETURN CLOB

Example

DECLARE
    element JSON_ELEMENT_T;
BEGIN
    element := JSON_ELEMENT_T.Parse('{"name": "Alice", "age": 30}');
    DBMS_OUTPUT.PUT_LINE(element.Stringify);
END;
/

Output:

{"age": 30, "name": "Alice"}

Inspect type and size

MEMBER FUNCTION is_Object(self IN JSON_ELEMENT_T)              RETURN BOOLEAN
MEMBER FUNCTION is_Array(self IN JSON_ELEMENT_T)               RETURN BOOLEAN
MEMBER FUNCTION get_Type(self IN JSON_ELEMENT_T, pos NUMBER)   RETURN VARCHAR2
MEMBER FUNCTION get_Type(self IN JSON_ELEMENT_T, key VARCHAR2) RETURN VARCHAR2
MEMBER FUNCTION get_Size(self IN JSON_ELEMENT_T)               RETURN NUMBER

Examples

is_Object:

DECLARE
    element JSON_ELEMENT_T;
BEGIN
    element := JSON_ELEMENT_T.Parse('{"name": "Alice", "age": 30}');
    IF element.is_Object THEN
        DBMS_OUTPUT.PUT_LINE('This is a JSON Object.');
    END IF;
END;
/

Output:

This is a JSON Object.

get_Type — returns the type name of the value at the given key:

DECLARE
    element      JSON_ELEMENT_T;
    element_type VARCHAR2(50);
BEGIN
    element      := JSON_ELEMENT_T.Parse('{"name": {"first": "Alice"}, "age": 30}');
    element_type := element.get_Type('name');  -- key "name" holds an object
    DBMS_OUTPUT.PUT_LINE('Type: ' || element_type);
END;
/

Output:

Type: OBJECT

get_Size — returns the number of keys in an object or elements in an array:

DECLARE
    element JSON_ELEMENT_T;
BEGIN
    element := JSON_ELEMENT_T.Parse('{"name": "Alice", "age": 30}');
    DBMS_OUTPUT.PUT_LINE('Size: ' || element.get_Size());
END;
/

Output:

Size: 2

Constructor functions

Pass a JSON string to initialize with data, or call the no-argument constructor to start with an empty object.

CONSTRUCTOR FUNCTION JSON_Object_T()             RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_Object_T(jsn VARCHAR2) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_Object_T(jsn CLOB)     RETURN SELF AS RESULT

Examples

Empty object:

DECLARE
    json_obj JSON_OBJECT_T := JSON_OBJECT_T();
BEGIN
    DBMS_OUTPUT.PUT_LINE(json_obj.to_String());
END;
/

Output:

{}

Object from a JSON string:

DECLARE
    json_obj JSON_OBJECT_T := JSON_OBJECT_T('{"name": "Alice", "age": 30}');
BEGIN
    DBMS_OUTPUT.PUT_LINE(json_obj.to_String());
END;
/

Output:

{"age": 30, "name": "Alice"}

Read and write key-value pairs

put — inserts a key-value pair. The value can be a scalar type or a JSON_ELEMENT_T.

MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, val VARCHAR2)
MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, val CLOB)
MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, val NUMBER)
MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, val DATE)
MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, val JSON_ELEMENT_T)

get and typed getters — retrieve the value at a key. Use typed getters (get_String, get_Number, etc.) to get the value in a specific type. Use get when you need to work with nested objects or arrays.

MEMBER FUNCTION get(self IN JSON_OBJECT_T, key VARCHAR2)        RETURN JSON_ELEMENT_T
MEMBER FUNCTION get_String(self IN JSON_OBJECT_T, key VARCHAR2) RETURN VARCHAR2
MEMBER FUNCTION get_Clob(self IN JSON_OBJECT_T, key VARCHAR2)   RETURN CLOB
MEMBER FUNCTION get_Number(self IN JSON_OBJECT_T, key VARCHAR2) RETURN NUMBER
MEMBER FUNCTION get_Date(self IN JSON_OBJECT_T, key VARCHAR2)   RETURN DATE
MEMBER FUNCTION get_Object(self IN JSON_OBJECT_T, key VARCHAR2) RETURN JSON_OBJECT_T
MEMBER FUNCTION get_Array(self IN JSON_OBJECT_T, key VARCHAR2)  RETURN JSON_ARRAY_T

Example — add a key and read it back:

DECLARE
    json_obj JSON_OBJECT_T;
    age      NUMBER;
BEGIN
    json_obj := JSON_OBJECT_T.Parse('{"name": "Alice"}');
    json_obj.put('age', 30);
    age := json_obj.get_Number('age');
    DBMS_OUTPUT.PUT_LINE(age);
END;
/

Output:

30

Retrieve all keys

get_Keys returns a JSON_KEY_LIST and get_Keys_As_Nchar returns a JSON_NKEY_LIST. Use the latter when key names contain multibyte characters.

MEMBER FUNCTION get_Keys(self IN JSON_OBJECT_T)          RETURN JSON_KEY_LIST
MEMBER FUNCTION get_Keys_As_Nchar(self IN JSON_OBJECT_T) RETURN JSON_NKEY_LIST

Example — get_Keys:

DECLARE
    key_list JSON_KEY_LIST;
    obj      JSON_OBJECT_T;
BEGIN
    obj      := JSON_OBJECT_T.Parse('{"name": "Alice", "age": 30}');
    key_list := obj.get_Keys();
    DBMS_OUTPUT.PUT_LINE(key_list(1));
    DBMS_OUTPUT.PUT_LINE(key_list(2));
END;
/

Output:

age
name

Example — get_Keys_As_Nchar:

DECLARE
    nkey_list JSON_NKEY_LIST;
    obj       JSON_OBJECT_T;
BEGIN
    obj       := JSON_OBJECT_T.Parse('{"name": "Alice", "age": 30}');
    nkey_list := obj.get_Keys_As_Nchar();
    DBMS_OUTPUT.PUT_LINE(nkey_list(1));
    DBMS_OUTPUT.PUT_LINE(nkey_list(2));
END;
/

Output:

name
age

Constructor functions

CONSTRUCTOR FUNCTION 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

Examples

Empty array:

DECLARE
    json_array JSON_ARRAY_T := JSON_ARRAY_T();
BEGIN
    DBMS_OUTPUT.PUT_LINE(json_array.to_String());
END;
/

Output:

[]

Array from a JSON string:

DECLARE
    json_array JSON_ARRAY_T := JSON_ARRAY_T('["Alice", 30]');
BEGIN
    DBMS_OUTPUT.PUT_LINE(json_array.to_String());
END;
/

Output:

["Alice", 30]

Read and write array elements

put — inserts a value at the given position. Set overwrite to TRUE to replace an existing element; the default is FALSE.

MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_ARRAY_T, pos NUMBER, val VARCHAR2,       overwrite BOOLEAN DEFAULT FALSE)
MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_ARRAY_T, pos NUMBER, val CLOB,           overwrite BOOLEAN DEFAULT FALSE)
MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_ARRAY_T, pos NUMBER, val NUMBER,         overwrite BOOLEAN DEFAULT FALSE)
MEMBER PROCEDURE put(self IN OUT NOCOPY JSON_ARRAY_T, pos NUMBER, val JSON_ELEMENT_T, overwrite BOOLEAN DEFAULT FALSE)

append — adds a value to the end of the array.

MEMBER PROCEDURE append(self IN OUT NOCOPY JSON_ARRAY_T, val VARCHAR2)
MEMBER PROCEDURE append(self IN OUT NOCOPY JSON_ARRAY_T, val CLOB)
MEMBER PROCEDURE append(self IN OUT NOCOPY JSON_ARRAY_T, val NUMBER)
MEMBER PROCEDURE append(self IN OUT NOCOPY JSON_ARRAY_T, val JSON_ELEMENT_T)

get and typed getters — retrieve the element at the given index.

MEMBER FUNCTION get(self IN JSON_ARRAY_T, pos NUMBER)         RETURN JSON_ELEMENT_T
MEMBER FUNCTION get_String(self IN JSON_ARRAY_T, pos NUMBER)  RETURN VARCHAR2
MEMBER FUNCTION get_Clob(self IN JSON_ARRAY_T, pos NUMBER)    RETURN CLOB
MEMBER FUNCTION get_Number(self IN JSON_ARRAY_T, pos NUMBER)  RETURN NUMBER
MEMBER FUNCTION get_Boolean(self IN JSON_ARRAY_T, pos NUMBER) RETURN BOOLEAN

Example — append an element and read it back:

DECLARE
    json_array JSON_ARRAY_T;
BEGIN
    json_array := JSON_ARRAY_T.Parse('["Alice"]');
    json_array.append(30);
    DBMS_OUTPUT.PUT_LINE(json_array.get_Number(1));
END;
/

Output:

30