如何利用单个Postgres查询创建多维数组？

没问题，我来帮你搞定这个需求！基于你给出的tb_school和tb_profile表结构，我们可以通过Postgres的聚合函数array_agg来实现嵌套的多维数组，下面分几种常见场景给出具体方案：

场景1：生成按学校分组的学生名字多维数组 #

如果想要得到类似[["John","Peter","Sam"], ["Susan","Jude"]]这样的结构（外层数组每个元素对应一所学校的学生名字数组），可以用两层array_agg嵌套实现：

SELECT array_agg(student_names ORDER BY school_id) AS school_students_array
FROM (
    -- 内层子查询：按学校分组，生成每个学校的学生名字一维数组
    SELECT 
        s.id AS school_id,
        array_agg(p.profile_name ORDER BY p.id) AS student_names
    FROM tb_school s
    JOIN tb_profile p ON s.id = p.school_id
    GROUP BY s.id, s.school_name
) AS school_students;

关键说明： #

• 内层子查询通过JOIN关联两张表，按学校分组后用array_agg把该校所有学生名字聚合成一维数组；
• 外层再用array_agg把这些一维数组打包成多维数组；
• 加ORDER BY是为了保证数组元素的顺序稳定（不管是学生名字的顺序，还是学校的顺序），避免每次查询结果顺序不一致。

场景2：生成包含学校名称+学生数组的结构化多维数组 #

如果想要更清晰的结构，比如每个元素是["School1", ["John","Peter","Sam"]]这样的二维数组，可以直接在聚合时组合学校名称和学生数组：

SELECT array_agg(
    -- 组合学校名称和学生数组，统一转为text类型数组
    ARRAY[s.school_name, array_agg(p.profile_name ORDER BY p.id)]::text[]
    ORDER BY s.id
) AS school_with_students_array
FROM tb_school s
JOIN tb_profile p ON s.id = p.school_id
GROUP BY s.id, s.school_name;

场景3：兼容无学生的学校（避免NULL） #

如果存在没有学生的学校，上面的JOIN会过滤掉这些学校，改用LEFT JOIN并配合COALESCE把空的学生数组转为空数组：

SELECT array_agg(
    ARRAY[
        s.school_name,
        -- 把NULL的学生数组替换为空数组
        COALESCE(array_agg(p.profile_name ORDER BY p.id), '{}'::text[])
    ]::text[]
    ORDER BY s.id
) AS school_with_students_array
FROM tb_school s
LEFT JOIN tb_profile p ON s.id = p.school_id
GROUP BY s.id, s.school_name;

这样即使某所学校没有学生，也会生成["SchoolX", []]这样的元素，不会出现NULL。

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