JSON_ARRAYAGG - OceanBase 数据库 V4.5.0

JSON_ARRAYAGG

更新时间：2026-04-08 13:52:37

描述

JSON_ARRAYAGG() 是一个聚合函数，它将所有列的数据转换成 JSON 数据，并且将所有的 JSON 数据组成一个单独的 JSON 数组结构。

语法

JSON_ARRAYAGG(expr [FORMAT JSON]
                   [ORDER SIBLINGS BY order_condition]
                   [ABSENT|NULL ON NULL,]
                   [RETURNING  CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
                   [STRICT]);

语法说明

JSON_ARRAYAGG() 函数语法说明如下：

FORMAT JSON ：声明该参数产生的值是 JSON 数据，因此不会在输出中存在引用('"')。
NULL|ABSENT ON NULL：表示仅当参数表达式的计算结果是 NULL 时生效。
- NULL ON NULL 将 JSON NULL 值用作当前数组元素。
- ABSENT ON NULL 将忽略该数组元素。
STRICT：用以验证输出结果是合法的 JSON 格式的数据。
RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]：用以指定返回值类型，其中VARCHAR2[size] 可以限制返回值的长度。
ORDER SIBLINGS BY: ORDERR BY 子句，其作用是对返回值中的 JSON 数组成员进行排序。

示例

# 使用默认参数
obclient> CREATE TABLE id_table (id NUMBER);
obclient> INSERT INTO id_table VALUES(624);
obclient> INSERT INTO id_table VALUES(null);
obclient> INSERT INTO id_table VALUES(925);
obclient> INSERT INTO id_table VALUES(585);
obclient> SELECT JSON_ARRAYAGG(id) ID_NUMBERS FROM id_table;
+---------------+
| ID_NUMBERS    |
+---------------+
| [624,925,585] |
+---------------+
1 row in set

# 使用FORMAT JSON 子句
obclient> CREATE TABLE json_table (json_doc VARCHAR2(100))
obclient> INSERT INTO json_table VALUES('[1]');
obclient> INSERT INTO json_table VALUES('[1,2]');
obclient> INSERT INTO json_table VALUES('[1,2,3]')
obclient> INSERT INTO json_table VALUES('{key:"value"}');
obclient> SELECT JSON_ARRAYAGG(json_doc FORMAT JSON) FROM json_table;
+-----------------------------------+
| JSON_ARRAYAGG(JSON_DOCFORMATJSON) |
+-----------------------------------+
| [[1],[1,2],[1,2,3],{key:"value"}] |
+-----------------------------------+
1 row in set

# 使用 ABSENT ON NULL 子句
obclient> SELECT JSON_ARRAYAGG(id ORDER BY id ABSENT ON NULL) FROM id_table;
+----------------------------------------+
| JSON_ARRAYAGG(IDORDERBYIDABSENTONNULL) |
+----------------------------------------+
| [585,624,925]                          |
+----------------------------------------+
1 row in set

# 使用 ORDER BY 子句
CREATE TABLE json_table (id NUMBER, text_data VARCHAR2(10), json_data JSON);
INSERT INTO json_table VALUES(624, 'test1', '[1]');
INSERT INTO json_table VALUES(null, 'test4', '[2]');
INSERT INTO json_table VALUES(925, 'test2', '[3]');
INSERT INTO json_table VALUES(585, 'test5', '[4]');
INSERT INTO json_table VALUES(585, 'test3', '[5]');
obclient> SELECT JSON_ARRAYAGG(text_data ORDER BY text_data) FROM json_table;
+-------------------------------------------+
| JSON_ARRAYAGG(TEXT_DATAORDERBYTEXT_DATA)  |
+-------------------------------------------+
| ["test1","test2","test3","test4","test5"] |
+-------------------------------------------+
1 row in set

obclient> SELECT JSON_ARRAYAGG(text_data ORDER BY id) FROM json_table;
+-------------------------------------------+
| JSON_ARRAYAGG(TEXT_DATAORDERBYID)         |
+-------------------------------------------+
| ["test5","test3","test1","test2","test4"] |
+-------------------------------------------+
1 row in set

obclient> SELECT JSON_ARRAYAGG(text_data ORDER BY id,text_data) FROM json_table;
+---------------------------------------------+
| JSON_ARRAYAGG(TEXT_DATAORDERBYID,TEXT_DATA) |
+---------------------------------------------+
| ["test3","test5","test1","test2","test4"]   |
+---------------------------------------------+
1 row in set

# 使用 STRICT 子句
CREATE TABLE json_table (json_doc VARCHAR2(100));
INSERT INTO json_table VALUES('[1]');
INSERT INTO json_table VALUES('[1,2]');
INSERT INTO json_table VALUES('[1,2,3]');
INSERT INTO json_table VALUES('{key:"value"}');

obclient> SELECT JSON_ARRAYAGG(json_doc FORMAT JSON STRICT) ID_NUMBERS FROM json_table;
OBE-00600: Invalid JSON text.
obclient> SELECT JSON_ARRAYAGG(json_doc FORMAT JSON) ID_NUMBERS FROM json_table;
+-----------------------------------+
| ID_NUMBERS                        |
+-----------------------------------+
| [[1],[1,2],[1,2,3],{key:"value"}] |
+-----------------------------------+
1 row in set