PostgreSQL数组匹配查询及结果计数错误问题求助

你的SQL语句中，unnest("keywords")会将数组的每个元素拆分为单独的行，比如id=1的数组包含3个元素，子查询会生成3条id=1的记录。后续的COUNT("id") OVER ()是对这3条记录计数，所以得到totalCount=3，但你需要的是原表中匹配条件的总行数，而非展开后的记录数。

可以先筛选出原表中符合条件的行，再对这些行统计总数，推荐使用EXISTS子查询结合unnest来判断数组中是否存在匹配元素，这样能准确找到原表的匹配行：

SELECT 
  "id", 
  "keywords", 
  COUNT(*) OVER () AS "totalCount"
FROM "keywordsList"
WHERE EXISTS (
  SELECT 1 
  FROM unnest("keywords") AS kw
  WHERE kw ILIKE '%apple%'
);

另一种简化写法（使用ANY操作符） #

也可以直接用ANY操作符检查数组元素是否匹配，避免子查询：

SELECT 
  "id", 
  "keywords", 
  COUNT(*) OVER () AS "totalCount"
FROM "keywordsList"
WHERE EXISTS (
  SELECT 1 
  WHERE ANY("keywords") ILIKE '%apple%'
);

执行上述语句后，搜索"apple"会得到你期望的结果：

id | keywords                              | totalCount
-------------------------------------------------
1  | [apple cake, apple pie, apple cookie] |  1

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