Json_agg函数

以下是 array_agg、json_agg 和 jsonb_agg 的具体使用示例。

准备示例数据

假设有一个员工表 employees：

sql

CREATE TABLE employees (id INT,dept_id INT,name TEXT
);INSERT INTO employees VALUES
(1, 101, '张三'),
(2, 101, '李四'),
(3, 102, '王五'),
(4, 102, '赵六'),
(5, 102, '孙七');

1. array_agg —— 聚合成 PostgreSQL 数组

sql

SELECT dept_id,array_agg(name) AS names_array
FROM employees
GROUP BY dept_id;

结果：

 

dept_id	names_array
101	{张三,李四}
102	{王五,赵六,孙七}

• 返回的是 PostgreSQL 的一维数组类型（text[]），可以用数组下标访问，例如 names_array[1] 得到“张三”。

2. json_agg —— 聚合成 JSON 数组

sql

SELECT dept_id,json_agg(name) AS names_json
FROM employees
GROUP BY dept_id;

结果：

 

dept_id	names_json
101	["张三", "李四"]
102	["王五", "赵六", "孙七"]

• 返回类型为 json，是一个标准的 JSON 数组格式。

3. jsonb_agg —— 聚合成 JSONB 数组

sql

SELECT dept_id,jsonb_agg(name) AS names_jsonb
FROM employees
GROUP BY dept_id;

结果：

 

dept_id	names_jsonb
101	["张三", "李四"]
102	["王五", "赵六", "孙七"]

• 返回类型为 jsonb，输出内容与 json_agg 看起来类似，但内部存储为二进制格式，支持更高效的查询和索引，并且会移除重复的键（但此处是简单字符串数组，所以没有区别）。

---

补充：聚合整行记录为 JSON

这三个函数不仅可以聚合单个列，也可以聚合整行（或指定结构）：

sql

SELECT dept_id,json_agg(employees) AS full_rows
FROM employees
GROUP BY dept_id;

这会将每个部门的员工记录以 JSON 对象的形式聚合到一个 JSON 数组中，每个对象包含 id、dept_id、name 字段。

 

 

这会将每个部门的员工记录以 JSON 对象的形式聚合到一个 JSON 数组中，每个对象包含 id、dept_id、name 字段。举例

 

 

 

好的，下面以 employees 表为例，展示如何将每个部门的整行记录聚合成一个 JSON 数组，数组中的每个元素都是一个包含 id、dept_id、name 字段的 JSON 对象。

示例：聚合整行记录为 JSON 数组

sql

SELECTdept_id,json_agg(employees) AS employees_json
FROM employees
GROUP BY dept_id;

执行结果：

 

dept_id	employees_json
101	[{"id":1,"dept_id":101,"name":"张三"}, {"id":2,"dept_id":101,"name":"李四"}]
102	[{"id":3,"dept_id":102,"name":"王五"}, {"id":4,"dept_id":102,"name":"赵六"}, {"id":5,"dept_id":102,"name":"孙七"}]

• json_agg(employees) 将 employees 表的整行（所有列）转换为 JSON 对象，然后按部门分组聚合为 JSON 数组。

• 每个 JSON 对象的键就是列名，值就是对应行的数据。

---

更灵活的构建：使用 json_build_object

如果你只想包含部分字段，或者想自定义键名，可以用 json_build_object 配合 json_agg：

sql

SELECTdept_id,json_agg(json_build_object('emp_id', id,'emp_name', name)) AS employees_json
FROM employees
GROUP BY dept_id;

结果：

 

dept_id	employees_json
101	[{"emp_id":1,"emp_name":"张三"}, {"emp_id":2,"emp_name":"李四"}]
102	[{"emp_id":3,"emp_name":"王五"}, {"emp_id":4,"emp_name":"赵六"}, {"emp_id":5,"emp_name":"孙七"}]

---