我有一个PostgreSQL映射表,它保存公司员工-团队领导和团队成员之间的关系。名为leaders
的表如下所示:
leader_id | employee_id
1 | 15
1 | 21
1 | 26
2 | 76
2 | 41
这两列都是
person
表的外键,该表包含name
、dob
等信息。目标是创建一个包含以下数据的JSON:
{
1: {name: "John Doe",
person_id: 1,
employees: {
15: {name: "Oliver Queen",
person_id: 15
},
21: {name: "Barry Alan",
person_id: 21
},
26: {name: "Solomon Rondon",
person_id: 26
},
}
},
2: {name: "Papi Hans",
person_id: 2,
employees: {
76: {name: "Ashley Young",
person_id: 76
},
41: {name: "Amberly Smith",
person_id: 41
}
}
},
}
通过编写一个带有两个连接的查询,将
person
表中的数据分别连接到leader_id
和employee_id
表中,然后逐行迭代结果来创建JSON,这样做非常容易。我的问题是,是否有一种方法来编写查询,以便它以准备好的JSON格式显示结果,这样我就不必逐行迭代来构建它了?如果有的话,效率会更高吗?
最佳答案
SELECT
json_object_agg(person_id, -- 5
-- 4
json_build_object('name', name, 'person_id', person_id, 'employees', employees)
)
FROM (
SELECT
l.leader_id as person_id,
p1.name as name,
json_object_agg(l.employee_id, -- 3
json_build_object('name', p2.name, 'person_id', p2.id) -- 2
) as employees
FROM
leader l
JOIN person p1 ON l.leader_id = p1.id -- 1
JOIN person p2 ON l.employee_id = p2.id
GROUP BY l.leader_id, p1.name
) s
加入
person
表以获取名称为每个员工创建JSON对象
通过将这些json对象按其领导者分组为一个json对象来聚合这些json对象
使用employee为领导创建JSON对象
将所有leader对象聚合为一个JSON对象
在这种情况下(4)可以缩短为
row_to_json(s)
进一步阅读:Postgres JSON,Postgres JSON aggregates