PostgreSQL中的json_agg()

PostgreSQL中的json_agg()是一个聚合函数，用于将多行数据聚合为一个JSON数组。以下是其主要特性和用法：

1. ‌基本功能‌
   将查询结果中的多行数据转换为JSON数组格式，每行数据作为数组中的一个元素24。

2. ‌结合json_build_object使用‌
   常与json_build_object配合，动态构建JSON对象并聚合为数组。例如：

   SELECT json_agg(json_build_object('key', column_name)) FROM table_name;

   这会生成[{"key": "value1"}, {"key": "value2"}]格式的结果24。

3. ‌分页与限制‌
   若需对聚合结果分页，可通过子查询或WITH子句实现：

   • ‌子查询方式‌：

     SELECT json_agg(subquery) FROM (SELECT * FROM table LIMIT 10 OFFSET 5) AS subquery;

   • ‌WITH子句方式‌：

     WITH limited_data AS (SELECT * FROM table LIMIT 10 OFFSET 5) SELECT json_agg(limited_data) FROM limited_data;

     这两种方法均能限制返回的JSON数组元素数量5。

4. ‌处理重复数据‌
   若聚合结果包含重复数据，需在聚合前使用DISTINCT或GROUP BY去重3。

5. ‌嵌套JSON聚合‌
   支持多层嵌套聚合，适用于复杂数据结构。例如，聚合学生及其课程信息：

   SELECT student_name, json_agg(json_build_object('course', course_name, 'teacher', teacher_name)) AS courses FROM student_courses GROUP BY student_name;

   输出形如[{"course": "Math", "teacher": "Teacher A"}, ...]48。

6. ‌排序控制‌
   可通过ORDER BY子句指定聚合顺序：

   SELECT json_agg(column_name ORDER BY sort_column) FROM table_name;

   确保JSON数组元素按指定规则排列2。

7. ‌数据类型转换‌
   若需将结果存储为jsonb类型，需显式转换以避免类型错误6。例如：

   INSERT INTO table (jsonb_column) VALUES (json_agg(...)::jsonb);

8. ‌性能优化‌
   对于大规模数据，建议结合索引使用，并避免在聚合函数内进行复杂计算78。