如何为PostgreSQL数组包含查询添加通配符支持？

要实现所有带通配符的搜索词都能在图片标签数组中找到匹配项的需求，你可以用以下几种方法替代不支持通配符的?&运算符：

方法1：多EXISTS子查询（静态搜索词场景） #

如果搜索词是固定的，直接用多个EXISTS子查询，每个子查询验证对应搜索词是否能在标签数组中找到匹配：

SELECT id
FROM images
WHERE 
  -- 匹配完全相等的'landscape'
  EXISTS (SELECT 1 FROM jsonb_array_elements_text(image_data->'tags') t WHERE t = 'landscape')
  -- 匹配以'cat'开头的标签（对应cat%通配符）
  AND EXISTS (SELECT 1 FROM jsonb_array_elements_text(image_data->'tags') t WHERE t LIKE 'cat%');

方法2：动态搜索词的通用写法 #

如果搜索词是动态传入的数组，用CTE（公共表表达式）结合双重否定的NOT EXISTS逻辑，确保没有任何一个搜索词无法匹配到标签：

WITH search_terms AS (
  -- 替换成你的动态搜索词数组
  SELECT unnest(array['landscape', 'cat%']) AS term
)
SELECT i.id
FROM images i
WHERE NOT EXISTS (
  -- 找到所有无法匹配当前图片标签的搜索词
  SELECT 1 FROM search_terms st
  WHERE NOT EXISTS (
    SELECT 1 FROM jsonb_array_elements_text(i.image_data->'tags') t
    WHERE t LIKE st.term
  )
);

方法3：分组统计匹配数 #

展开标签数组后，通过分组统计匹配到的不同搜索词数量，确保数量等于总搜索词数：

WITH search_terms AS (
  SELECT unnest(array['landscape', 'cat%']) AS term
)
SELECT i.id
FROM images i
-- 展开图片的所有标签
JOIN jsonb_array_elements_text(i.image_data->'tags') t ON true
-- 匹配搜索词
JOIN search_terms st ON t LIKE st.term
GROUP BY i.id
-- 确保所有搜索词都匹配到了
HAVING COUNT(DISTINCT st.term) = (SELECT COUNT(*) FROM search_terms);

性能优化建议 #

如果图片和标签数据量较大，上述方法的性能可能会受影响，建议：

• 拆分标签到关联表：新建image_tags表，存储image_id和tag字段，给tag字段建B-tree索引（支持LIKE 'xxx%'前缀匹配），查询效率会远高于jsonb数组操作。
• jsonb索引优化：如果坚持用jsonb，可以给image_data->'tags'建GIN索引，同时结合jsonb_path_query实现正则前缀匹配：

  SELECT id
  FROM images
  WHERE 
    jsonb_path_exists(image_data, '$.tags[*] ? (@ like_regex "^landscape")')
    AND jsonb_path_exists(image_data, '$.tags[*] ? (@ like_regex "^cat")');
  

  这里^表示前缀匹配，对应LIKE 'cat%'的效果。

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