PolarDB:JSON functions and expressions

Scope

The minor engine version of your PolarDB for PostgreSQL (Compatible with Oracle) cluster must be 2.0.14.18.37.0 or later.

Validate JSON data

You can use the IS JSON expression to determine whether data is in a valid JSON format.

Syntax

expr IS [ NOT ] JSON

Examples

• Validate a valid JSON string

  -- Determine whether the string is valid JSON.
  SELECT '{"name": "Alice", "age": 30}' IS JSON;

  The expected result is:

   polar_is_json 
  ---------------
   t

• Validate a JSON string

  -- Use IS NOT JSON to identify invalid JSON.
  SELECT '{name}' IS NOT JSON;

  The expected result is:

   polar_is_not_json 
  -------------------
   t

• Handle NULL values

  IS JSON and IS NOT JSON also return NULL when the input is NULL.

  -- Handle NULL values.
  SELECT NULL IS JSON;
  SELECT NULL IS NOT JSON;

Build JSON objects

You can use the JSON_OBJECT expression to combine key-value pairs or table column data into a JSON object.

Prerequisites

You must enable the polar_enable_ora_json_funcs parameter. Navigate to the Settings Management > Parameters page in the PolarDB console to set cluster parameters.

Syntax

JSON_OBJECT (
    [ { [ KEY ] key_expr VALUE val_expr | key_expr [ ':' val_expr ] } [ FORMAT JSON ] , ... ]
    [ { NULL | ABSENT } ON NULL ] -- JSON_on_null_clause
    [ RETURNING { JSON | VARCHAR2 | CLOB | BLOB } ] -- JSON_returning_clause
    [ STRICT ]
    [ WITH UNIQUE KEYS ]
)

Parameters

Examples

• Basic usage: Build a simple object

  -- Combine several key-value pairs into a JSON object.
  SELECT JSON_OBJECT(KEY 'name' VALUE 'Tim', 'age' : 20);

  The expected result is:

      polar_json_object    
  -------------------------
   {"name":"Tim","age":20}

• Advanced usage: Handle NULL values and embedded JSON

  -- Handle NULL values and embed an existing JSON string.
  SELECT JSON_OBJECT(
    'user_id' : 101,
    'profile' : '{"city": "Shanghai"}' FORMAT JSON,
    'manager_id' : NULL,          
    'department_id' : NULL
    ABSENT ON NULL
    STRICT
    WITH UNIQUE KEYS) Result
  FROM dual;

  The expected result is:

                       result                     
  ------------------------------------------------
   {"user_id":101,"profile":{"city": "Shanghai"}}

Serialize JSON data

You can use JSON_SERIALIZE to transform JSON data into a string for easier reading or transmission.

Prerequisites

You must enable the polar_enable_ora_json_funcs parameter. You can navigate to the Settings Management > Parameters page in the PolarDB console to set cluster parameters.

Syntax

JSON_SERIALIZE (
    expr
    [ RETURNING { JSON | VARCHAR2 | CLOB | BLOB } ] -- JSON_returning_clause
    [ PRETTY ]
)

Parameters

Examples

-- Format a compact JSON string into a readable form.
SELECT JSON_SERIALIZE('{"a":[1,2,3,4], "b":{"c": "d"}}' RETURNING VARCHAR2 PRETTY);

The expected result is:

 jsonb_pretty 
--------------
 {
   "a": [
     1,
     2,
     3,
     4
   ],
   "b": {
     "c": "d"
   }
 }

Aggregate into a JSON array

You can use the JSON_ARRAYAGG aggregate function to aggregate a column from a multi-row query result into a JSON array.

Prerequisites

You must enable the polar_enable_ora_json_funcs parameter. You can navigate to the Settings Management > Parameters page in the PolarDB console to set cluster parameters.

Syntax

JSON_ARRAYAGG ( expr )

Examples

Assume that you have an employee table and want to aggregate the ages of all employees into a JSON array.

-- Aggregate the age column from multiple rows into a JSON array.
SELECT JSON_ARRAYAGG(age)
FROM (
  SELECT '12' AS age FROM DUAL UNION ALL
  SELECT '-12.3' FROM DUAL UNION ALL
  SELECT '13.5' FROM DUAL UNION ALL
  SELECT '15.7' FROM DUAL
);

The expected result is:

     polar_json_arrayagg      
------------------------------
 ["12","-12.3","13.5","15.7"]

Use JSON types in PL/SQL

In a PL/SQL environment, you can use the TREAT AS expression to cast a JSON object to a specified JSON type. Currently, type conversion is supported only between the JSON_ELEMENT_T base class and its derived classes, such as JSON_OBJECT_T and JSON_ARRAY_T.

Scenarios

This expression lets you operate on JSON data in an object-oriented way within a PL/SQL code block and switch between different JSON type views.

Examples

The following example shows how to convert between JSON_OBJECT_T and JSON_ELEMENT_T.

-- This example shows how to convert a specific JSON object type to a generic element type and then convert it back in PL/SQL.
DECLARE
  l_json_element JSON_ELEMENT_T;
  l_json_object  JSON_OBJECT_T;
  l_json_object2 JSON_OBJECT_T;
BEGIN
  -- 1. Create and fill a JSON_OBJECT_T object.
  l_json_object := JSON_OBJECT_T();
  l_json_object.put('name', 'Kevin');
  l_json_object.put('number', 35);

  -- 2. Use TREAT AS to convert it to the generic JSON_ELEMENT_T type.
  l_json_element := TREAT(l_json_object AS JSON_ELEMENT_T);
  DBMS_OUTPUT.PUT_LINE('JSON_ELEMENT_T from JSON_OBJECT_T: ' || l_json_element.to_string);

  -- 3. Convert the generic element type back to the specific JSON_OBJECT_T type.
  l_json_object2 := TREAT(l_json_element AS JSON_OBJECT_T);
  DBMS_OUTPUT.PUT_LINE('JSON_OBJECT_T from JSON_ELEMENT_T: ' || l_json_object2.to_string);
END;
/