oracle的json_arrayagg的用法和例子

JSON_ARRAYAGG 是 Oracle 数据库中用于将多行数据聚合为一个 JSON 数组的函数。这个函数对于将查询结果转换为 JSON 格式特别有用，尤其是在 RESTful API 或需要 JSON 输出的应用中。

语法

sql

JSON_ARRAYAGG ( [ DISTINCT | UNIQUE ] expr [ ON NULL { NULL | ABSENT | EMPTY_ARRAY | EMPTY_OBJECT } ]

               [ ORDER BY { order_by_clause | NULL } ]

               [ FORMAT JSON ]

               [ PRETTY ]

               [ RETURNING CLOB | VARCHAR2(size) ]

               [ NULL ON NULL ]

               [ STRICT ]

             )

 

expr：要聚合的表达式，可以是任何可以求值为 JSON 对象的表达式。

ON NULL：指定当 expr 为 NULL 时如何处理。

ORDER BY：指定排序顺序。

FORMAT JSON：指定输入已经是 JSON 格式，不需要额外处理。

PRETTY：美化输出的 JSON 格式（在 12c R2 及更高版本中可用）。

RETURNING：指定返回的数据类型，默认为 VARCHAR2(4000)。

NULL ON NULL：如果所有输入值都是 NULL，则返回 NULL（在 12c R2 及更高版本中可用）。

STRICT：如果任何输入值不是有效的 JSON，则抛出异常（在 12c R2 及更高版本中可用）。

例子

假设我们有一个名为 employees 的表，包含员工的 id 和 name：

sql

CREATE TABLE employees (

    id NUMBER PRIMARY KEY,

    name VARCHAR2(100)

);

 

INSERT INTO employees VALUES (1, 'Alice');

INSERT INTO employees VALUES (2, 'Bob');

INSERT INTO employees VALUES (3, 'Charlie');

 

 

我们可以使用 JSON_ARRAYAGG 来获取所有员工的名字并作为一个 JSON 数组返回：

 

sql

SELECT JSON_ARRAYAGG(name) AS employee_names

FROM employees;

 

这将返回：

 

json

["Alice", "Bob", "Charlie"]

 

如果我们想要按名字排序并返回更长的数据类型（例如 CLOB），可以这样做：

 

sql

SELECT JSON_ARRAYAGG(name ORDER BY name) AS employee_names

FROM employees

RETURNING CLOB;

 

 

如果某个员工的名字为 NULL，并且我们想要在 JSON 数组中省略这些 NULL 值，可以使用 ON NULL ABSENT：

sql

-- 假设我们有一个名字为 NULL 的员工

INSERT INTO employees VALUES (4, NULL);

 

SELECT JSON_ARRAYAGG(name ON NULL ABSENT) AS employee_names

FROM employees;

 

这将返回：

json

["Alice", "Bob", "Charlie"]

注意：NULL 值已被省略。