PostgreSQL查询优化：匹配指定标签时返回媒体文件的全部标签

你的核心问题是原查询在WHERE子句中直接过滤了标签，导致关联时仅保留匹配dog/cat的标签记录，最终聚合结果只显示这两个标签。要实现「匹配任意目标标签即返回该媒体全部标签」的需求，关键要先筛选符合条件的媒体，再拉取该媒体的所有标签，而非直接过滤标签。

优化后的查询语句（推荐用EXISTS写法，更直观） #

SELECT m.media_id, m.name, array_agg(DISTINCT t.tag) FILTER (WHERE t.date_deleted IS NULL) AS tags
FROM media m
LEFT JOIN media_tags mt USING (media_id)
LEFT JOIN tags t USING (tag_id)
WHERE m.date_deleted IS NULL
  -- 仅筛选存在目标标签且标签未删除的媒体
  AND EXISTS (
      SELECT 1
      FROM media_tags mt_sub
      JOIN tags t_sub USING (tag_id)
      WHERE mt_sub.media_id = m.media_id
        AND t_sub.tag = ANY(array['dog','cat'])
        AND t_sub.date_deleted IS NULL
  )
GROUP BY m.media_id, m.name;

另一种子查询筛选写法 #

SELECT m.media_id, m.name, array_agg(DISTINCT t.tag) FILTER (WHERE t.date_deleted IS NULL) AS tags
FROM media m
-- 先筛选出符合条件的媒体ID集合
JOIN (
    SELECT DISTINCT mt.media_id
    FROM media_tags mt
    JOIN tags t USING (tag_id)
    WHERE t.tag = ANY(array['dog','cat'])
      AND t.date_deleted IS NULL
      AND EXISTS (
          SELECT 1 FROM media m_sub 
          WHERE m_sub.media_id = mt.media_id 
            AND m_sub.date_deleted IS NULL
      )
) filtered_media ON m.media_id = filtered_media.media_id
-- 关联获取该媒体的所有标签
LEFT JOIN media_tags mt USING (media_id)
LEFT JOIN tags t USING (tag_id)
WHERE m.date_deleted IS NULL
GROUP BY m.media_id, m.name;

改动说明 #

1. 原查询问题：t.tag = ANY(array['dog','cat'])放在主WHERE中，会直接过滤掉非dog/cat的标签记录，导致聚合时只能拿到这两个标签。
2. 优化逻辑：
   • 用EXISTS子查询（或子查询筛选媒体ID）先判断「当前媒体是否存在未删除的dog/cat标签」，仅保留符合条件的媒体。
   • 主查询关联所有标签时不再过滤标签类型，因此array_agg能聚合该媒体下所有未删除的标签。
3. 测试数据验证结果：
   • Media1返回标签：['dog','bird','sheep','horse']
   • Media2返回标签：['dog','cat','cow']
   • Media3返回标签：['cat','horse','rabbit']
   • Media4因无匹配标签被排除

内容的提问来源于stack exchange，提问作者RobertW