在postgres中的聚合函数中的sql

对于我的问题,我们有一个模式,一张照片有很多标签和许多评论.所以如果我有一个查询,我想要所有的注释和标签,它会将行乘以一起.所以如果一张照片有2个标签和13个评论,我得到26行的那张照片：

SELECT
        tag.name,comment.comment_id
FROM
        photo
        LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
        LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
        LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id

这对大多数事情都很好,但这意味着如果我的GROUP BY然后是json_agg(tag.*),我得到第一个标签的13个副本,第二个标签的13个副本.

SELECT json_agg(tag.name) as tags
FROM
        photo
        LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
        LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
        LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id

相反,我想要一个仅“郊区”和“城市”的数组,如下所示：

[
      {"tag_id":1,"name":"suburban"},{"tag_id":2,"name":"city"}
 ]

我可以使用json_agg(DISTINCT tag.name),但是当我想将整行作为json时,这只会产生一个标签名称数组.我想要json_agg(DISTINCT ON(tag.name)tag.*),但这显然不是有效的sql.

如何在Postgres中的聚合函数中模拟DISTINCT ON？

@H_502_16@

WITH tags AS ( SELECT photo.photo_id,json_agg(row_to_json(tag.*)) AS tags FROM photo LEFT OUTER JOIN photo_tag on photo_tag.photo_id = photo.photo_id LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id GROUP BY photo.photo_id ),comments AS ( SELECT photo.photo_id,json_agg(row_to_json(comment.*)) AS comments FROM photo LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id GROUP BY photo.photo_id ) SELECT COALESCE(tags.photo_id,comments.photo_id) AS photo_id,tags.tags,comments.comments FROM tags FULL OUTER JOIN comments ON tags.photo_id = comments.photo_id

SELECT photo.photo_id,to_json(array_agg(DISTINCT tag.*)) AS tags,to_json(array_agg(DISTINCT comment.*)) AS comments FROM photo LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id LEFT OUTER JOIN photo_tag on photo_tag.photo_id = photo.photo_id LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id GROUP BY photo.photo_id

在postgres中的聚合函数中的sql – DISTINCT ON

猜你在找的设计模式相关文章