PostgreSQL json_object_agg() 函数使用指南

PostgreSQL json_object_agg() 函数是一个聚合函数，它返回一个由一组键值对组成的 JSON 对象。

json_object_agg() 语法

这里是 PostgreSQL json_object_agg() 的语法：

json_object_agg(key_expr, value_expr) -> JSON

我们通常在 SQLite 中按如下方式使用 json_object_agg() 函数：

SELECT json_object_agg(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

参数

key_expr

必需的。一个用于生成 JSON 对象中键值对的键的列名或者表达式。

value_expr

必需的。一个用于生成 JSON 对象中键值对的值的列名或者表达式。

返回值

PostgreSQL json_object_agg() 函数返回一个对象，它包含了一个分组中由指定的列或者表达式组成的键值对。其中， key_expr 的值作为键值对中的键， value_expr 的值作为键值对中的值并使用 to_json() 函数转为 JSON 值。

json_object_agg() 示例

为了演示 json_object_agg() 的用法，我们使用以下 UNION 语句模拟一个表：

SELECT 'Tim' name, 'Math' subject, 'A' grade
UNION
SELECT 'Tim' name, 'English' subject, 'B' grade
UNION
SELECT 'Tom' name, 'Math' subject, 'B' grade
UNION
SELECT 'Tom' name, 'English' subject, 'A' grade;

name  subject  grade
----  -------  -----
Tim   English  B
Tim   Math     A
Tom   English  A
Tom   Math     B

这里，我们拥有了关于用户成绩的一些行。其中 name 列中是用户的名称， subject 列中是科目的名称， grade 列中是一个科目的成绩。

假如，要想以用户为单位（具有相同名称的用户视为一个用户），将每一个用户的所有成绩都放在一个 JSON 对象（科目作为键，成绩作为值）中，您可以使用 GROUP BY 子句按照 name 分组，并使用 json_object_agg() 函数将每组中科目和成绩构建为一个 JSON 对象。下面的语句实现了这个需求：

SELECT
    t.name,
    json_object_agg(t.subject, t.grade) grades
FROM (
    SELECT 'Tim' name, 'Math' subject, 'A' grade
    UNION
    SELECT 'Tim' name, 'English' subject, 'B' grade
    UNION
    SELECT 'Tom' name, 'Math' subject, 'B' grade
    UNION
    SELECT 'Tom' name, 'English' subject, 'A' grade
) t
GROUP BY t.name;

 name |              grades
------+-----------------------------------
 Tim  | { "English" : "B", "Math" : "A" }
 Tom  | { "English" : "A", "Math" : "B" }
(2 rows)

这里，

• 对于 Tim，他的成绩(英语 B 和 数学 A)都被聚合成一个 JSON 对象 { "English" : "B", "Math" : "A" }。
• 对于 Tom，他的成绩(英语 A 和 数学 B)都被聚合成一个 JSON 对象 { "English" : "A", "Math" : "B" }。