PostgreSQL-选择具有某些标签的项目，但在结果中保留所有标签

Question

我的标签搜索有问题，数据库正确返回包含标签的图像，但所有其他标签都被删除。我将所有标签存储在单独的表“标签映射”中。

这是我搜索“tag1”的结果（图像实际上具有比 tag1 更多的标签）：

[{"postID": 1, "images": {"imageID": 1, "tags": ["tag1"]}}]

这是我想要搜索“tag1”的结果：

[{"postID": 1, "images": {"imageID": 1, "tags": ["tag1", "tag2", "tag3"]}}]

这是生成第一个结果的查询：

WITH image_tags AS (
  SELECT images."post id", json_build_object (
    'imageID', images."image id",
    'tags', json_agg("tag map".tag)
  ) AS image,
  FROM images
  JOIN "tag map" ON images."image id" = "tag map"."image id"
  WHERE "tag map".tag = ALL ($1)
  GROUP BY images."post id", images."image id"
)
SELECT posts."post id" AS "postID", json_agg(image_tags.image) AS images 
FROM posts
JOIN image_tags ON posts."post id" = image_tags."post id"
GROUP BY posts."post id"

Answer 1

答案是将其分组为子查询并在其后使用 WHERE。我更改了表结构，因此查询与 OP 中的查询有点不同。

SELECT * FROM (
  SELECT posts.*, json_agg(DISTINCT images.*) AS images, array_agg(DISTINCT "tag map".tag) AS tags
  FROM posts
  JOIN images ON posts."postID" = images."postID"
  JOIN "tag map" ON posts."postID" = "tag map"."postID"
  GROUP BY posts."postID"
) AS posts
WHERE tags @> $1