对查询执行横向连接时,我遇到了一个奇怪的结果
我有以下表格结构
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, useridwith 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 trueDbFiddle。
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 trueDbFiddle.
更多推荐
发布评论