如何为整数数组实现PostgreSQL的jsonb ?|运算符功能？

问题根源 #

?|运算符仅支持text[]类型的右参数，直接传入整数数组会因类型不匹配报错，以下是几种可行的解决方法：

方法一：整数数组转文本数组后使用?| #

将整数数组强制转换为text[]，利用JSONB元素的文本表示与字符串形式整数匹配的特性，直接复用?|运算符：

SELECT '[1, 2, 3]'::jsonb ?| array[1, 2]::text[];
-- 返回结果：t

方法二：使用jsonb_path_exists函数（PostgreSQL 12+） #

通过JSON路径表达式直接匹配整数元素，无需类型转换，逻辑更直观：

-- 方式1：使用参数绑定
SELECT jsonb_path_exists('[1, 2, 3]'::jsonb, '$[*] ? (@ == $elem)', '{"elem": [1,2]}');

-- 方式2：直接在路径中指定数组
SELECT jsonb_path_exists('[1, 2, 3]'::jsonb, '$[*] ? (@ == any([1,2]))');
-- 两种方式均返回：t

方法三：自定义运算符（适合高频使用场景） #

如果需要频繁进行此类判断，可以自定义一个支持jsonb与integer[]的?|运算符，内部封装类型转换逻辑：

-- 先创建核心处理函数
CREATE OR REPLACE FUNCTION jsonb_contains_any_int(jsonb, integer[])
RETURNS boolean AS $$
BEGIN
  RETURN $1 ?| $2::text[];
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 创建自定义运算符
CREATE OPERATOR ?| (
  LEFTARG = jsonb,
  RIGHTARG = integer[],
  FUNCTION = jsonb_contains_any_int
);

-- 直接使用自定义运算符
SELECT '[1, 2, 3]'::jsonb ?| array[1, 2];
-- 返回结果：t

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