PostgreSQL：实现返回含外键关联数组的JSON函数需求

以下是满足需求的函数创建语句及使用说明：

函数定义 #

CREATE OR REPLACE FUNCTION json_fk_full(p_student Student)
RETURNS json AS $$
DECLARE
    v_teacher_id integer := p_student.ID_TEACHER;
    v_students json;
    v_teachers json;
BEGIN
    -- 聚合当前教师关联的所有学生数据
    SELECT json_agg(row_to_json(s))
    INTO v_students
    FROM Student s
    WHERE s.ID_TEACHER = v_teacher_id;

    -- 聚合对应教师的数据
    SELECT json_agg(row_to_json(t))
    INTO v_teachers
    FROM Teacher t
    WHERE t.ID = v_teacher_id;

    -- 构造目标JSON结构
    RETURN json_build_object(
        'student', v_students,
        'teacher', v_teachers
    );
END;
$$ LANGUAGE plpgsql STABLE;

使用方式 #

单个学生查询 #

执行以下语句，传入指定学生行，返回其所属教师对应的所有学生及教师信息：

SELECT json_fk_full(t) FROM Student t WHERE t.ID = 1;

去重全局查询 #

若直接执行SELECT json_fk_full(t) FROM Student t;会返回与学生行数相同的重复结果，只需一份最终JSON的话，可添加DISTINCT去重：

SELECT DISTINCT json_fk_full(t) FROM Student t;

返回结果示例 #

执行后将得到符合需求的JSON结构：

{
  "student": [
    {"ID":1, "Name":"Jhon", "ID_TEACHER":1},
    {"ID":2, "Name":"Maria", "ID_TEACHER":1}
  ],
  "teacher": [{"ID":1, "Name":"Peter"}]
}

关键说明 #

• 利用row_to_json将表行数据转换为JSON对象，json_agg将多个对象聚合为JSON数组；
• 标记函数为STABLE，表示相同输入会返回相同结果，PostgreSQL可据此进行查询优化；
• 函数逻辑为：根据传入学生的ID_TEACHER，聚合所有同教师的学生数据及对应教师数据，最终组合为目标JSON结构。

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