JSON函数和表达式_云原生数据库 PolarDB(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;
/