在查询中使用2个横向连接在json

在查询中使用2个横向连接在json_agg（）中重复行(repeated rows in json_agg() in query with 2 lateral joins)

对查询执行横向连接时，我遇到了一个奇怪的结果

我有以下表格结构

task->id comment -> id , taskId, comment tasklink -> taskId, type, userid

具有单个任务记录（id 10），1条评论记录（“row1”，“测试评论”）和5个tasklink记录（全部具有taskid 10）

我期待这个查询

select task.id, json_agg(json_build_object('id',c.id, 'user',c.comment)) as comments, json_agg(json_build_object('type',b.type, 'user',b.userid)) as users FROM task left join lateral (select c.* from comment c where task.id = c.taskid) c on true left join lateral (select b.* from taskuserlink b where task.id = b.taskid) b on true where task.id = 10 GROUP BY task.id ;

回来

id | comments | users --------------------------------------------------------------------- 10 "[{"id":"row1","user":"a test comment"}]" "[{"type":"updatedBy","user":1},"type":"closedBy","user":5},"type":"updatedBy","user":5},"type":"createdBy","user":5},{"type":"ownedBy","user":5}]"

相反，我得到了这个

id | comments | users 10 "[{"id":"row1","user":"a test comment"},{"id":"row1","user":"a test comment"},{"id":"row1","user":"a test comment"},{"id":"row1","user":"a test comment"},{"id":"row1","user":"a test comment"}]" "[{"type":"updatedBy","user":1},{"type":"closedBy","user":5},{"type":"updatedBy","user":5},{"type":"createdBy","user":5},{"type":"ownedBy","user":5}]"

即对于每个link行， comment行都是重复的

我在想我错过了一些非常明显的东西，但是因为我刚刚开始使用Postgres（和sql），所以我有点难过

我希望得到关于我哪里出错的一些指导

I have a strange result when performing a lateral join on a query

I have the following table structure

task->id comment -> id , taskId, comment tasklink -> taskId, type, userid

with a single task record (id 10), 1 comment record ("row1", "a test comment") and 5 tasklink records (all with taskid 10)

I expected this query

select task.id, json_agg(json_build_object('id',c.id, 'user',c.comment)) as comments, json_agg(json_build_object('type',b.type, 'user',b.userid)) as users FROM task left join lateral (select c.* from comment c where task.id = c.taskid) c on true left join lateral (select b.* from taskuserlink b where task.id = b.taskid) b on true where task.id = 10 GROUP BY task.id ;

to return

id | comments | users --------------------------------------------------------------------- 10 "[{"id":"row1","user":"a test comment"}]" "[{"type":"updatedBy","user":1},"type":"closedBy","user":5},"type":"updatedBy","user":5},"type":"createdBy","user":5},{"type":"ownedBy","user":5}]"

instead, I got this

id | comments | users 10 "[{"id":"row1","user":"a test comment"},{"id":"row1","user":"a test comment"},{"id":"row1","user":"a test comment"},{"id":"row1","user":"a test comment"},{"id":"row1","user":"a test comment"}]" "[{"type":"updatedBy","user":1},{"type":"closedBy","user":5},{"type":"updatedBy","user":5},{"type":"createdBy","user":5},{"type":"ownedBy","user":5}]"

ie , for every link row, the comment row is duplicated

I am thinking that I am missing something really obvious, but as I have only just started using Postgres (and sql ) I'm a little stumped

I would appreciate some guidance on where I'm going wrong

最满意答案

将聚合移动到子查询中：

select id, comments, users from task t left join lateral ( select json_agg(json_build_object('id',c.id, 'user',c.comment)) as comments from comment c where t.id = c.taskid ) c on true left join lateral ( select json_agg(json_build_object('type',b.type, 'user',b.userid)) as users from taskuserlink b where t.id = b.taskid ) b on true

DbFiddle。

Move the aggregates into subqueries:

select id, comments, users from task t left join lateral ( select json_agg(json_build_object('id',c.id, 'user',c.comment)) as comments from comment c where t.id = c.taskid ) c on true left join lateral ( select json_agg(json_build_object('type',b.type, 'user',b.userid)) as users from taskuserlink b where t.id = b.taskid ) b on true

DbFiddle.