Oracle JSON_OBJECTAGG() 函数使用指南

Oracle JSON_OBJECTAGG() 是一个内置的聚合函数，它为每个键值对构建一个对象成员，并返回一个包含这些对象成员的单个 JSON 对象。

Oracle JSON_OBJECTAGG() 语法

这里是 Oracle JSON_OBJECTAGG() 函数的语法：

JSON_OBJECTAGG
  ( [ KEY ] key_str VALUE expr [ FORMAT JSON ]
    [ JSON_on_null_clause ] [ JSON_agg_returning_clause ]
    [ STRICT ] [ WITH UNIQUE KEYS ]
  )

参数

key_str

必需的。它指定属性键名，它是区分大小写的文本文字面量。

expr

必需的。它指定属性值。对于 expr，可以指定任何评估为 SQL 数值文字、文本文字、日期或时间戳的表达式。日期和时间戳数据类型以 ISO 8601 日期格式的 JSON 字符串形式打印在生成的 JSON 对象或数组中。如果 expr 评估为数值文字，则生成的属性值为 JSON 数值；否则，生成的属性值为用双引号括起来的区分大小写的 JSON 字符串值。

FORMAT JSON

可选的。使用此可选子句指示输入字符串为 JSON，并因此不会在输出中引用。

JSON_on_null_clause

可选的。您可以使用此子句指定函数在 expr 评估为 null 时的行为。

  • NULL ON NULL - 当指定 NULL ON NULL 时，给定键的值将使用 JSON NULL 值。
  • ABSENT ON NULL - 如果指定此子句，则函数将在 JSON 对象中省略属性键值对。
JSON_agg_returning_clause

可选的。您可以使用此子句指定此函数返回的字符串数据类型。您可以指定以下数据类型：

  • VARCHAR2[(``size [BYTE,CHAR])]，在 SQL 的其他地方指定 VARCHAR2 数据类型时，需要指定大小。但是在此子句中，可以省略大小。默认值为 VARCHAR2(4000)。
  • CLOB 返回包含单字节或多字节字符的字符大对象。
  • BLOB 返回 AL32UTF8 字符集的二进制大对象。
STRICT

可选的。您可以指定 STRICT 子句以验证 JSON 生成函数的输出是否正确。如果检查失败，则会引发语法错误。

WITH UNIQUE KEYS

可选的。您可以使用 WITH UNIQUE KEYS 来确保生成的 JSON 对象具有唯一的键。

返回值

Oracle JSON_OBJECTAGG() 函数返回一个 JSON 对象，其中包含通过参数指定的所有的键值对。

如果任意一个参数为 NULL， JSON_OBJECTAGG() 将返回 NULL。

Oracle JSON_OBJECTAGG() 示例

这个示例使用以下语句模拟一个数据表：

SELECT 'Tim' name, 'English' subject, 80 score FROM DUAL
UNION ALL
SELECT 'Tim', 'Maths', 90 FROM DUAL
UNION ALL
SELECT 'Lucy', 'Maths', 96 FROM DUAL;

输出：

NAME    SUBJECT       SCORE
_______ __________ ________
Tim     English          80
Tim     Maths            90
Lucy    Maths            96

如果您需要从上面的数据集获取所有的成绩，请使用下面的语句：

SELECT JSON_OBJECTAGG(KEY subject VALUE score) score
FROM (
    SELECT 'Tim' name, 'English' subject, 80 score FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Maths', 90 FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Maths', 96 FROM DUAL
);

输出：

SCORE
_______________________________________
{"English":80,"Maths":90,"Maths":96}

如果您需要从上面的数据集获取每个人的成绩，请使用下面的带有 GROUP BY 子句的语句：

SELECT name, JSON_OBJECTAGG(KEY subject VALUE score) score
FROM (
    SELECT 'Tim' name, 'English' subject, 80 score FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Maths', 90 FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Maths', 96 FROM DUAL
)
GROUP BY name;

输出：

NAME    SCORE
_______ ____________________________
Lucy    {"Maths":96}
Tim     {"English":80,"Maths":90}

结论

Oracle JSON_OBJECTAGG() 是一个内置的聚合函数，它为每个键值对构建一个对象成员，并返回一个包含这些对象成员的单个 JSON 对象。