创建父子多对多关系查询

Question

您好，我很难在Postgres中创建一个查询，以获得父子形式的多对多关系。有两张表：

1. "项目">
   项目表
2. "服务">
   服务表

它们与第三个project_service表连接
project_service

查询SELECT projects.id_project, projects.title, projects.level, services.id_service, services.service FROM projects, services, project_service WHERE projects.id_project = project_service.id_project AND services.id_service = project_service.id_service返回此表：
查询结果表

如何编写一个查询来以父子形式获取此表，如下所示：

[
{ "id": "1000", "level": "Projects", "title": "Project 1", "children": [
{ "id": "2000", "service": "Service 1},
{ "id": "2001", "service": "Service 2},
{ "id": "2002", "service": "Service 3},
{ "id": "2003", "service": "Service 4}
]},
{ "id": "1001", "level": "Projects", "title": "Project 2", "children": [
{ "id": "2004", "service": "Service 5}
]}]

感谢的帮助

Answer 1

这就是你的意思吗？

with svc_json as (
select id_service, to_jsonb(services) as svc
from services
), together as (
select p.*, jsonb_agg(s.svc) as children  
from project_service ps
join svc_json s on s.id_service = ps.id_service
join projects p on p.id_project = ps.id_project
group by p.id_project, p.title, p.level
)
select jsonb_pretty(jsonb_agg(to_jsonb(together)))
from together;
jsonb_pretty               
-----------------------------------------
[                                      +
{                                  +
"level": "Projects",           +
"title": "Project 1",          +
"children": [                  +
{                          +
"service": "Service 1",+
"id_service": 2000     +
},                         +
{                          +
"service": "Service 2",+
"id_service": 2001     +
},                         +
{                          +
"service": "Service 3",+
"id_service": 2002     +
},                         +
{                          +
"service": "Service 4",+
"id_service": 2003     +
}                          +
],                             +
"id_project": 1000             +
},                                 +
{                                  +
"level": "Projects",           +
"title": "Project 2",          +
"children": [                  +
{                          +
"service": "Service 5",+
"id_service": 2004     +
}                          +
],                             +
"id_project": 1001             +
}                                  +
]
(1 row)