如何在PostgreSQL查询中添加额外列并返回嵌套学生列表？

你可以通过聚合函数结合JSON构造函数来实现这个需求，以下是几种实用方案：

方案1：返回单班级行+学生JSON数组 #

这种方式为每个班级返回一行数据，listOfStudents字段是包含学生信息的JSON数组：

SELECT 
    c.id,
    json_agg(
        json_build_object(
            'id', s.id,
            'name', s.name,
            'classId', s.classId
        )
    ) AS listOfStudents
FROM Classes c
LEFT JOIN Students s ON c.id = s.classId
GROUP BY c.id;

关键细节： #

• LEFT JOIN确保没有学生的班级也会被返回，此时listOfStudents为[]空数组
• json_build_object用于构造每个学生的结构化JSON对象，可按需增减字段
• json_agg将当前班级的所有学生对象聚合成一个数组
• GROUP BY c.id保证每个班级仅生成一条记录

方案2：直接返回完整的Classroom数组 #

如果需要直接得到你要求的「Classroom数组」结构，可在外层嵌套json_agg：

SELECT json_agg(
    json_build_object(
        'id', c.id,
        'listOfStudents', json_agg(
            json_build_object(
                'id', s.id,
                'name', s.name,
                'classId', s.classId
            )
        )
    )
) AS classrooms
FROM Classes c
LEFT JOIN Students s ON c.id = s.classId
GROUP BY c.id;

可选：使用PostgreSQL原生数组类型 #

若更倾向于数据库原生数组而非JSON，可改用array_agg，返回的是记录类型数组：

SELECT 
    c.id,
    array_agg(ROW(s.id, s.name, s.classId)) AS listOfStudents
FROM Classes c
LEFT JOIN Students s ON c.id = s.classId
GROUP BY c.id;

通常JSON格式在应用层处理更灵活，因此优先推荐前两种方案。

内容的提问来源于stack exchange，提问作者branperr