Oracle JSON_ARRAYAGG() 函数使用指南

Oracle JSON_ARRAYAGG() 是一个内置的聚合函数，它以 SQL 表达式列作为输入，将每个表达式转换为一个 JSON 值，并返回包含这些 JSON 值的单个 JSON 数组。

Oracle JSON_ARRAYAGG() 语法

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

JSON_ARRAYAGG
  ( expr [ FORMAT JSON ]  [ order_by_clause ]
    [ JSON_on_null_clause ] [ JSON_agg_returning_clause ]
    [ STRICT ]
  )

参数

expr

必需的。 您可以指定任何求值为 JSON 对象、JSON 数组、数字文字、文本文字或 null 的 SQL 表达式。该函数将数字文字转换为 JSON 数值，将文本文字转换为 JSON 字符串值。

FORMAT JSON

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

order_by_clause

可选的。该子句允许您在语句返回的 JSON 数组中对 JSON 值进行排序。

JSON_on_null_clause

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

  • NULL ON NULL - 如果指定了此子句，则函数返回 JSON null 值。
  • ABSENT ON NULL - 如果指定了此子句，则函数从 JSON 数组中省略该值。这是默认设置。
JSON_agg_returning_clause

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

  • VARCHAR2[(size [BYTE,CHAR])]: 在 SQL 的其他地方指定 VARCHAR2 数据类型时，必须指定大小。但是，在此子句中，您可以省略大小。
  • CLOB

如果省略此子句，或者指定了 VARCHAR2 但省略了 size 值，则 JSON_ARRAYAGG 返回类型为 VARCHAR2(4000) 的字符串。

STRICT

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

返回值

Oracle JSON_ARRAYAGG() 函数返回一个 JSON 数组，它包括根据给定的表达式的所有的计算结果。

Oracle JSON_ARRAYAGG() 示例

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

SELECT 'Tim' name, 'Football' hobby FROM DUAL
UNION ALL
SELECT 'Tim', 'Basketball' FROM DUAL
UNION ALL
SELECT 'Lucy', 'Piano' FROM DUAL
UNION ALL
SELECT 'Lucy', 'Violin' FROM DUAL;

输出：

NAME    HOBBY
_______ _____________
Tim     Football
Tim     Basketball
Lucy    Piano
Lucy    Violin

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

SELECT JSON_ARRAYAGG(hobby)
FROM (
    SELECT 'Tim' name, 'Football' hobby FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Basketball' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Piano' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Violin' FROM DUAL
);

输出：

JSON_ARRAYAGG(HOBBY)
_____________________________________________
["Football","Basketball","Piano","Violin"]

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

SELECT name, JSON_ARRAYAGG(hobby) hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Basketball' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Piano' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Violin' FROM DUAL
)
GROUP BY name;

输出：

NAME    HOBBIES
_______ ____________________________
Lucy    ["Piano","Violin"]
Tim     ["Football","Basketball"]

当然，您也可以将所有的爱好按照升序排序：

SELECT name, JSON_ARRAYAGG(hobby ORDER BY hobby) hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Basketball' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Piano' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Violin' FROM DUAL
)
GROUP BY name;

输出：

NAME    HOBBIES
_______ ____________________________
Lucy    ["Piano","Violin"]
Tim     ["Basketball","Football"]

结论

Oracle JSON_ARRAYAGG() 是一个内置的聚合函数，它以 SQL 表达式列作为输入，将每个表达式转换为一个 JSON 值，并返回包含这些 JSON 值的单个 JSON 数组。