PolarDB:JSON composite types

Static functions

The static function parse creates a JSON_ELEMENT_T object based on a JSON string. The function prototype is as follows:

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;
/

JSON to string conversion

The following function prototypes are used to convert a JSON_ELEMENT_T object to a string:

• stringify

  MEMBER FUNCTION stringify(self IN JSON_ELEMENT_T) RETURN VARCHAR2

• to_String

  MEMBER FUNCTION to_String(self IN JSON_ELEMENT_T) RETURN VARCHAR2

• to_Clob

  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"}

JSON type and size determination

The following function prototypes are available:

• is_Object: Determines whether the data is a JSON object.

  MEMBER FUNCTION is_Object(self IN JSON_ELEMENT_T) RETURN BOOLEAN

• is_Array: Determines whether the data is a JSON array.

  MEMBER FUNCTION is_Array(self IN JSON_ELEMENT_T)  RETURN BOOLEAN

• get_Type: Used to determine the type of the current JSON object and individual elements of a JSON array.

  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

• get_Size: Retrieves the number of keys/elements in an object or array.

  MEMBER FUNCTION get_Size(self IN JSON_ELEMENT_T) RETURN NUMBER

Example

• 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

  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');  -- Output: OBJECT
      DBMS_OUTPUT.PUT_LINE('Type: ' || element_type);
  END;
  /

  Output:

  Type: OBJECT

• get_Size

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

  Output:

  Size: 2

Constructor functions

The JSON_OBJECT_T constructor function prototype is as follows. If the parameter is not specified, an empty object without key-value pairs is constructed. If a parameter is specified, a JSON_OBJECT_T object is constructed with the string as key-value pairs.

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

Example

• Empty parameter:

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

  Output:

  {}

• Non-empty parameter:

  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"}

Key-value pair read/write

• put: Adds a key-value pair to a JSON object, where the value can be a common basic type or a JSON object. The related function prototypes are as follows:

  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: Retrieves the value corresponding to a given key, which can be of different data types. The related function prototypes are as follows:

  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

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 object containing all keys. The related function prototype is as follows:

  MEMBER FUNCTION get_Keys(self IN JSON_OBJECT_T) RETURN JSON_KEY_LIST

• get_Keys_As_Nchar: Returns a JSON_NKEY_LIST object containing all keys. The related function prototype is as follows:

  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}');
      -- Extract key names
      key_list := obj.get_keys();
      DBMS_OUTPUT.PUT_LINE(key_list(1)); -- Output: name
      DBMS_OUTPUT.PUT_LINE(key_list(2)); -- Output: age
  END;
  /

  Output:

  age
  name

• get_Keys_As_Nchar

  DECLARE
      nkey_list JSON_NKEY_LIST;
      obj JSON_OBJECT_T;
  BEGIN
      obj := JSON_OBJECT_T.Parse('{"name": "Alice", "age": 30}');
      -- Get JSON keys (as NVARCHAR2)
      nkey_list := obj.get_keys_as_nchar();
      DBMS_OUTPUT.PUT_LINE(nkey_list(1)); -- Output: name
      DBMS_OUTPUT.PUT_LINE(nkey_list(2)); -- Output: age
  END;
  /

  Output:

  name
  age

Constructor functions

The JSON_ARRAY_T constructor function prototype is as follows. If the parameter is not specified, an empty array without elements is constructed. If a parameter is specified, a JSON_ARRAY_T object is constructed with the string.

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

Example

• The parameter is not specified

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

  Output:

  []

• The parameter is not empty

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

  Output:

  ["Alice", 30]

Array read/write

• put

  Inserts a given element into a JSON array. The element can be a common basic type or a JSON object or array. The related function prototypes are as follows:

  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

  Appends a given element to a JSON array. The element can be a common basic type or a JSON object or array. The related function prototypes are as follows:

  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

  Retrieves the value corresponding to a given index, which can be of different data types. The related function prototypes are as follows:

  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

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)); -- Output: 30
END;
/

Output:

30