PostgreSQL-选择带有特定标记的项目，但在结果中保留所有标记

Question

我的标签搜索有问题，数据库正确返回包含标签的图像，但所有其他标签都被剥离了。我把所有的标签都存储在一个单独的表中；标签映射"；。

这是我搜索的结果"；标签1"；(图像实际上有比标签1更多的标签(：

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

这是我想要搜索的结果"；标签1"：

[{"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