PostgreSQL jsonb @>操作符需支持含重复元素的全包含匹配

PostgreSQL的@>操作符对jsonb数组做的是集合层面的包含检查，只判断目标数组的元素是否都存在于原数组中，完全不考虑元素的重复次数——这就是为什么'[1,2,3]'::jsonb @> '[1,1]'会返回true，它只看到1存在于原数组，不管出现几次。

要实现包含所有元素及对应重复次数的匹配，需要通过统计每个元素的出现次数来对比，下面是两种可行方案：

1. 直接用SQL查询实现 #

通过CTE分别统计原数组和目标数组中每个元素的出现次数，再检查目标数组的所有元素次数都不超过原数组的对应次数：

WITH target_counts AS (
  SELECT elem, count(*) AS cnt
  FROM jsonb_array_elements('[1, 1]'::jsonb) AS elem
  GROUP BY elem
), source_counts AS (
  SELECT elem, count(*) AS cnt
  FROM jsonb_array_elements('[1, 2, 3]'::jsonb) AS elem
  GROUP BY elem
)
SELECT NOT EXISTS (
  SELECT 1
  FROM target_counts t
  LEFT JOIN source_counts s ON t.elem = s.elem
  -- 要么原数组没有该元素，要么目标数组的次数超过原数组
  WHERE s.cnt IS NULL OR t.cnt > s.cnt
);

• 当目标数组是[1,2]时，查询返回true
• 当目标数组是[1,1]时，查询返回false

2. 封装为自定义函数（推荐复用） #

如果需要频繁使用这个逻辑，可以封装成PL/pgSQL函数，调用更方便：

CREATE OR REPLACE FUNCTION jsonb_array_contains_all_with_duplicates(source jsonb, target jsonb)
RETURNS boolean AS $$
BEGIN
  -- 先校验输入是否都是jsonb数组
  IF jsonb_typeof(source) != 'array' OR jsonb_typeof(target) != 'array' THEN
    RETURN FALSE;
  END IF;

  RETURN NOT EXISTS (
    SELECT 1
    FROM (
      SELECT elem, count(*) AS cnt
      FROM jsonb_array_elements(target) AS elem
      GROUP BY elem
    ) t
    LEFT JOIN (
      SELECT elem, count(*) AS cnt
      FROM jsonb_array_elements(source) AS elem
      GROUP BY elem
    ) s ON t.elem = s.elem
    WHERE s.cnt IS NULL OR t.cnt > s.cnt
  );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

调用示例：

-- 返回true
SELECT jsonb_array_contains_all_with_duplicates('[1,2,3]'::jsonb, '[1,2]'::jsonb);

-- 返回false
SELECT jsonb_array_contains_all_with_duplicates('[1,2,3]'::jsonb, '[1,1]'::jsonb);

注意事项 #

• 这个逻辑对jsonb数组中的复杂元素（比如嵌套对象）也有效，因为jsonb类型可以直接分组对比
• 如果数组元素是数值类型，jsonb_array_elements返回的是jsonb类型，但分组对比时会自动处理类型匹配

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