Postgres的 - 查询表多对多的关系
问题描述:
我的模式是由这样的:Postgres的 - 查询表多对多的关系
channel
- >channels_categories
- >category
一个通道可以具有很多类,和类别可以属于许多信道。
当我查询频道时,我想获得他们的频道类别。
无论如何,如果我使用JOIN
我会简单地获得每个类别的重复频道。
[
{
"channel_name": "Channel1",
"category_name": "Category1",
"category_id": "1"
},
{
"channel_name": "Channel1",
"category_name": "Category2"
"category_id": "2"
}
]
理想的结果格式(JSON)会是这样的:
{
channel_name: 'Channel1',
categories: [{/**category**/}, ....]
}
有没有一种方法,我可以实现这个结果的格式只是SQL?
答
这是完全未经测试,但我已经看了手册JSON功能,它看起来像以下可能的工作:
select jsonb_build_object(
'channel_name', channel.name,
'categories', jsonb_agg(
jsonb_build_object(
'category_id', category.id,
'category_name', category.name
)
)
)
from channel
join channels_categories on channel.id = channel_id
join category on category.id = category_id
group by channel.id
这假定channel
有一个名为id
主键。
答
我刚刚注意到,你真的想在JSON格式的结果,这个答案是关于合并和合并属性到JSON列表。
您可以使用string_agg和concat对于类别和CATEGORY_ID这样的结合列表:
select channel_name,
concat('[', string_agg(category_name, ','), ']') as category_names,
concat('[', string_agg(cast(category.id as text), ','), ']') as category_ids
from
channel inner join channel_category on channel.id = channel_category.channel_id
inner join category on channel_category.category_id = category.id
group by channel_name;
其中产生的结果那样:
| channel_name | category_names | category_ids |
| channel1 | [category1,category2]| [1,2] |
对于一个完整的JSON结果melpomene's答案似乎适合你的需求比这更好。
只需使用[string_agg](https://www.postgresql.org/docs/9.1/static/functions-aggregate.html)作为类别和[concat](https://www.postgresql。 org/docs/9.1/static/functions-string.html)添加方括号。 – RonaldFindling
让我明白,这将在类别只是一个字符串的情况下工作?如果我在结果中也有类别ID,该怎么办? – pietrovismara
如果你还想加入他们的字符串,你应该可以做同样的事情。像'select channel_name,string_agg(category_name,','),sting_agg(category_id,',')...'我现在没有postgresDB,现在就试试看吧 – RonaldFindling