时间戳

首页 > 解决方案 > SQL查询嵌套的一对多关系，作为聚合数组？

sql - SQL查询嵌套的一对多关系，作为聚合数组？

问题描述

我正在使用 Postgres 作为我的数据库的看板。通常我会使用 MongoDB 来做类似的事情，但我正在尝试练习和提高我对关系数据库的理解。

所以我有棋盘，它可以有很多通道，通道可以有很多卡。Boards, lanes, and cards 是数据库中的 3 个表。Lanes 有一个 boardId，它是一个 board.id 值，卡片有一个 laneId，它是一个 lane.id 值。

我想通过它的 id 查询一个板，但也让它包含一个通道数组。然后，我希望每个通道都包含一组卡片。像这样：

{
id:123
title: 'Board title',
lanes: [{id: 0, title: 'Lane title', cards: [{id: 0, text: 'Card text'}]}, {id: 1, title: 'Lane title', cards: [{id: 1, text: 'Card text'}, {id: 2, text: 'Card text'}]}]
}

我有一个查询的第一部分，该查询获取一个板，然后创建一个车道数组。不确定这是否是“正确”的方法，但这里有一个例子，寻找一个 id 为“123”的板：

select "boards"."id" as "boardId", "boards"."title" as "boardTitle", ARRAY_AGG(json_build_object('id', lanes.id, 'title', lanes.title)) as lanes from "boards" inner join "lanes" on "boards"."id" = "lanes"."boardId" and "boards"."id" = 123 group by "boards"."id"

但我不确定如何将卡片作为通道数组中每个元素的卡片数组包含在内。我的猜测是我可以添加另一个连接，例如"cards" on "lanes"."id" = "cards"."laneId"......但是我不知道如何在 json_build_object 中包含每个通道的卡片。

标签： sqlpostgresql

解决方案

最好从数据库中接受 json 以便将行放入数组中。

我将使用 CTE 构建它以使其尽可能清晰：

with agg_lanes as (
  select lane_id as id, jsonb_agg(cards) as "cards"
    from cards 
   group by lane_id
), agg_boards as (
  select b.id, b.title, jsonb_agg(a) as "lanes"
    from agg_lanes a
    join lanes l on l.id = a.id
    join boards b on b.id = l.board_id
   group by b.id, b.title
)
select to_json(a)
  from agg_boards a
;

在这里工作小提琴。

推荐阅读