对于我的问题，我们有一个架构，其中一张照片有很多标签和很多评论。因此，如果我有一个需要所有评论和标签的查询，它会将这些行相乘。因此，如果一张照片有 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？

最佳答案

我发现的最简单的事情是使用 DISTINCT 而不是 jsonb(不是 json!)。 (jsonb_build_object 创建 jsonb 对象)

SELECT 
   JSON_AGG(
       DISTINCT jsonb_build_object('tag_id', photo_tag.tag_id, 
                                  'name', 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

https://stackoverflow.com/questions/30077639/