如何从单个postgres查询中创建一个多维数组。
我有3个表格具有以下列:
-
tb_school(id,school_name( 例如:
[ {id:"1", school_name:"School1"}, {id:"2", school_name:"School2"} ]
-
tb_profile(id,profile_name,school_id( 例如:
[ {id:"1", profile_name:"John", school_id:"1"}, {id:"2", profile_name:"Peter", school_id:"1"}, {id:"3", profile_name:"Sam", school_id:"1"}, {id:"4", profile_name:"Susan", school_id:"2"}, {id:"5", profile_name:"Jude", school_id:"2"}, {id:"6", profile_name:"Kim", school_id:"2"} ]
-
tb_Article(ID,aptif_name,profile_id(例如:
[ {id:"1", article_name:"Headline News", profile_id:"1"}, {id:"2", article_name:"Sports Recap", profile_id:"2"}, {id:"3", article_name:"Weather", profile_id:"3"}, {id:"4", article_name:"Arts", profile_id:"4"}, {id:"5", article_name:"Other", profile_id:"5"}, {id:"6", article_name:"Example", profile_id:"6"} ]
我希望查询返回结果lat看起来像这样:
[
{school_name:"School1", people:[
{profile_name:"John", articles:[
article_name:"Headline News"
]},
{profile_name:"Peter", articles:[
article_name:"Sports Recap"
]},
{profile_name:"Sam", articles:[
article_name:"Weather"
]},
]},
{school_name:"School2", people:[
{profile_name:"Susan", articles:[
article_name:"Arts"
]},
{profile_name:"Jude", articles:[
article_name:"Other"
]},
{profile_name:"Kim", articles:[
article_name:"Example"
]},
]}
]
我知道可以通过3个单独的选择查询来实现此目标。我想知道的是,这可以通过一个Postgres查询来实现吗?
注意:我对PostgreSQL不好。可能做出了错误的假设,并且代码可能不打算用于生产。
我几乎确定您不能仅仅导致一个嵌套对象。但是您可以收到JSON。所以
SELECT
json_build_object(
'school_name', school_name,
'people', (
SELECT
json_agg(json_build_object(
'profile_name', profile_name,
'articles', (
SELECT
json_agg(json_build_object(
'article_name', article_name
))
FROM tb_article
WHERE profile_id = tb_profile.facid
)
))
FROM tb_profile
WHERE school_id = tb_school.id
)
) AS result
FROM tb_school
它将为您提供JSON,您可以JSON.parse
。
如果要测试,则可以使用此学习服务。我用此代码使用了它。
SELECT
json_build_object(
'name', firstname,
'slots', (
SELECT
json_agg(json_build_object(
'number', slots,
'facility', (
SELECT
json_agg(json_build_object(
'name', name
))
FROM cd.facilities
WHERE facid = cd.bookings.facid
)
))
FROM cd.bookings
WHERE memid = cd.members.memid
)
) AS result
FROM cd.members
它可能会有所帮助或提供更多想法。