如何统计JSONB数组字段中指定字符串出现次数？含GIN索引疑问

统计每个名字的出现次数 #

要统计指定数组中每个名字在Team表names字段（JSONB数组）中的出现次数，同时保留出现次数为0的名字，可采用以下两种SQL方案：

方法一：拆分所有数组元素统计（适合小表） #

WITH input_names AS (
  -- 将输入的名字数组拆分为单独行
  SELECT unnest('["Karen", "Sarah", "Tom"]'::text[]) AS name
),
all_team_names AS (
  -- 拆分所有Team记录的names数组为单个名字
  SELECT jsonb_array_elements_text(names) AS name
  FROM Team
)
-- 关联输入名字和拆分后的团队名字，统计次数
SELECT 
  inp.name,
  COUNT(an.name) AS count
FROM input_names inp
LEFT JOIN all_team_names an ON inp.name = an.name
GROUP BY inp.name
ORDER BY inp.name;

方法二：利用JSONB操作符计算（适合大表，可结合索引优化） #

如果Team表数据量较大，拆分所有数组会带来性能损耗，可通过JSONB数组差集操作快速计算单条记录中目标名字的出现次数，再求和：

WITH input_names AS (
  SELECT unnest('["Karen", "Sarah", "Tom"]'::text[]) AS name
)
SELECT 
  inp.name,
  -- 计算每条包含目标名字的记录中，该名字的出现次数并求和
  (SELECT SUM(jsonb_array_length(names) - jsonb_array_length(names - to_jsonb(inp.name)))
   FROM Team
   WHERE names @> to_jsonb(inp.name)) AS count
FROM input_names inp;

该查询通过names @> to_jsonb(inp.name)先筛选出包含目标名字的记录，再用数组长度差计算该名字在每条记录中的出现次数，最后求和得到总次数。

GIN索引的作用 #

GIN索引对这个场景有明显帮助，尤其是表数据量大时：

• 给names字段创建GIN索引：CREATE INDEX idx_team_names_gin ON Team USING GIN (names);
• 上述方法二中的names @> to_jsonb(inp.name)条件会触发GIN索引，快速定位到包含目标名字的记录，避免全表扫描，大幅减少需处理的数据量，提升查询效率。
• 注意：方法一中的拆分所有数组操作无法直接利用GIN索引，因此大表下更推荐方法二结合GIN索引的方案。

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