使用Oracle相容的JSON函數 - 雲原生資料庫 PolarDB

PolarDB提供與Oracle相容的JSON函數和運算式，用於在資料庫層面處理半結構化資料，例如驗證資料格式、動態構建JSON對象或將多行資料彙總為JSON數組。

適用範圍

您的PolarDB PostgreSQL版（相容Oracle）叢集的修訂版本需為2.0.14.18.37.0及以上

說明

您可在控制台查看核心小版本號碼，也可以通過SHOW polardb_version;語句查看。如未滿足核心小版本要求，請升級核心小版本。

驗證JSON資料

使用IS JSON運算式判斷資料是否為有效JSON格式。

文法

expr IS [ NOT ] JSON

使用樣本

驗證合法的JSON字串

-- 判斷字串是否為有效JSON
SELECT '{"name": "Alice", "age": 30}' IS JSON;

預期返回結果：

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

驗證非法的JSON字串

-- 使用 IS NOT JSON 判斷非法JSON
SELECT '{name}' IS NOT JSON;

預期返回結果：

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

處理NULL值
IS JSON和IS NOT JSON在處理NULL輸入時，結果同樣為NULL。
```
-- 示範對NULL值的處理
SELECT NULL IS JSON;
SELECT NULL IS NOT JSON;
```

構建JSON對象

使用 JSON_OBJECT 運算式將索引值對或表列資料群組合成JSON對象。

前置條件

需開啟polar_enable_ora_json_funcs參數。您可以前往PolarDB控制台的配置與管理 > 參數配置頁面來設定叢集參數。

文法

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 ]
)

參數說明

參數/子句	說明
`KEY key_expr VALUE val_expr`	定義一個索引值對。 `key_expr`：鍵名。用字串常量指定屬性鍵名。 `val_expr`：索引值。指定屬性的值，可以是各種結果值為數字、字串、日期或時間戳記的運算式。如果val_expr被省略，則表示`key_expr`是一個表列，列名作為`KEY`，列的值作為`VALUE`。 `key_expr`和 `val_expr`之間用VALUE關鍵字或冒號分隔，二者組成一個索引值對。如果有多個索引值對，則用逗號分隔。
`FORMAT JSON`	可選子句，對每個索引值對都可以指定，表示`val_expr`本身也是 JSON，在產生最終對象時不應再對其進行轉義。
`JSON_on_null_clause`	可選子句，指定`val_expr`為NULL時的行為： `NULL ON NULL`：在最終的JSON對象中保留該鍵，並將其值設為`null`。 `ABSENT ON NULL`（預設）：在最終的JSON對象中省略該索引值對。
`JSON_returning_clause`	可選子句，指定傳回值的類型，可選的類型如下：說明 PolarDB目前與Oracle不相容的特性為：僅提供文法相容，而不改變運算式的傳回值類型，始終返回JSON類型。然而，PolarDB中支援JSON類型到`VARCHAR2`、`CLOB`和`BLOB`類型的隱式轉換，因此返回的JSON類型通常可以被隱式轉換為目標類型，這通常不會影響業務的使用。 `RETURNING JSON` `RETURNING VARCHAR2` `RETURNING VARCHAR2(<type_mode>)` `RETURNING CLOB` `RETURNING BLOB`
`STRICT`	可選子句，嚴格檢查運算式的結果是否符合JSON格式，檢查失敗則報錯。預設不檢查。
`WITH UNIQUE KEYS`	可選子句，檢查是否具有重複的KEY，存在則報錯。預設不檢查。

使用樣本

基礎用法：構建簡單對象

-- 將幾個索引值對組合成一個JSON對象
SELECT JSON_OBJECT(KEY 'name' VALUE 'Tim', 'age' : 20);

預期返回結果：

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

進階用法：處理NULL值和內嵌JSON

-- 示範如何處理NULL值並嵌入已有的JSON字串
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;

預期返回結果：

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

序列化JSON資料

使用JSON_SERIALIZE將JSON類型資料轉換為字串，以便於閱讀或傳輸。

前置條件

需開啟polar_enable_ora_json_funcs參數。您可以前往PolarDB控制台的配置與管理 > 參數配置頁面來設定叢集參數。

文法

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

參數說明

參數/子句	說明
`expr`	需要轉換的JSON資料，需為字串常量、`JSON`、`VARCHAR2`、`CLOB`或`BLOB`類型。
`JSON_returning_clause`	可選子句，指定傳回值的類型，如果不指定，則預設為`VARCHAR2(4000)`。如果指定了`VARCHAR2`，但是沒有指定`type_mode`，預設也是`VARCHAR2(4000)`。 `RETURNING JSON` `RETURNING VARCHAR2` `RETURNING VARCHAR2(<type_mode>)` `RETURNING CLOB` `RETURNING BLOB`
`PRETTY`	可選子句，對輸出的JSON字串進行格式化（增加換行和縮排）。預設不格式化。說明 PolarDB目前與Oracle不相容的特性為：如果JSON資料中存在重複KEY，且指定STRICT子句，則重複KEY會丟失。

使用樣本

-- 將一個緊湊的JSON字串格式化為易讀的形式
SELECT JSON_SERIALIZE('{"a":[1,2,3,4], "b":{"c": "d"}}' RETURNING VARCHAR2 PRETTY);

預期返回結果：

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

彙總為JSON數組

使用JSON_ARRAYAGG彙總函式將多行查詢結果中的某一列彙總成JSON數組。

前置條件

需開啟polar_enable_ora_json_funcs參數。您可以前往PolarDB控制台的配置與管理 > 參數配置頁面來設定叢集參數。

文法

JSON_ARRAYAGG ( expr )

使用樣本

假設有一張員工表，希望將所有員工的年齡彙總成一個JSON數組。

-- 將多行資料中的 age 列彙總為一個 JSON 數組
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
);

預期返回結果：

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

在PL/SQL中使用JSON類型

TREAT AS運算式用於在PL/SQL環境中，將一個JSON對象強制轉換為指定的JSON類型。目前僅支援在JSON_ELEMENT_T基類及其衍生類別（如JSON_OBJECT_T、JSON_ARRAY_T）之間進行類型轉換。

適用情境

在PL/SQL代碼塊中以物件導向的方式操作JSON，且需要在不同JSON類型視圖之間切換。

使用樣本

在JSON_OBJECT_T和JSON_ELEMENT_T之間轉換。

-- 示範如何在 PL/SQL 中將一個具體的 JSON 物件類型轉換為通用的元素類型，然後再轉換回來。
DECLARE
  l_json_element JSON_ELEMENT_T;
  l_json_object  JSON_OBJECT_T;
  l_json_object2 JSON_OBJECT_T;
BEGIN
  -- 1. 建立並填充一個 JSON_OBJECT_T 對象
  l_json_object := JSON_OBJECT_T();
  l_json_object.put('name', 'Kevin');
  l_json_object.put('number', 35);

  -- 2. 使用 TREAT AS 將其轉換為通用的 JSON_ELEMENT_T 類型
  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. 再將通用的元素類型轉換回具體的 JSON_OBJECT_T 類型
  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;
/