如何过滤PostgreSQL中jsonb列特定值出现指定次数的行？

针对你给出的jsonb列结构（键为星期，值为出勤类型），要筛选出from_home值恰好出现3次的行，这里提供几种实用方案：

方案1：展开键值对后统计计数 #

这是最直观且性能表现稳定的方法，通过jsonb_each_text将jsonb对象拆分为键值对行，再统计符合条件的值的数量：

SELECT *
FROM your_table
WHERE (
  SELECT COUNT(*)
  FROM jsonb_each_text(your_jsonb_column)
  WHERE value = 'from_home'
) = 3;

jsonb_each_text会把每个jsonb对象的键值对转换成文本类型的行数据，子查询中直接统计value等于目标值的行数，匹配次数时就返回对应行。

方案2：用JSON路径提取匹配值后统计长度 #

如果偏好更简洁的写法，可以用PostgreSQL的JSON路径功能，直接提取所有匹配的from_home值并组成数组，再判断数组长度：

SELECT *
FROM your_table
WHERE jsonb_array_length(
  jsonb_path_query_array(your_jsonb_column, '$.* ? (@ == "from_home")')
) = 3;

jsonb_path_query_array的路径表达式$.*表示取所有键对应的值，? (@ == "from_home")筛选出等于目标值的元素，最终返回一个json数组，jsonb_array_length获取数组长度后判断是否符合要求。

方案3：提取值数组后筛选统计 #

也可以先提取所有值为文本数组，再筛选出目标值并统计数量：

SELECT *
FROM your_table
WHERE array_length(
  array(
    SELECT unnest(jsonb_values_text(your_jsonb_column))
    WHERE unnest = 'from_home'
  ), 1
) = 3;

jsonb_values_text提取所有值为文本数组，unnest将数组拆分为行，筛选出目标值后重新组成数组，用array_length获取长度判断。

性能建议 #

如果表数据量较大，优先选择方案1，PostgreSQL查询优化器对这种子查询计数的方式处理更高效，不需要生成额外的中间数组结构。如果需要处理更复杂的匹配规则（比如多值匹配、模糊匹配），JSON路径的方案（方案2）会更灵活。

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