sql - Postgres - 查询具有多对多关系的表

Question

我的架构是这样组成的:

channel -> channels_categories -> 类别

一个 channel 可以有多个类别，类别可以属于多个 channel 。

当我查询 channel 时，我想获取 channel 类别。

无论如何，如果我使用 JOIN，我将简单地为每个类别复制 channel 。

[
  {
    "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 就可以实现这种结果格式？

Answer 1

这完全未经测试，但我查看了 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 的主键。