PostgreSQL 14中JSONB数字数组使用jsonb_exists_any匹配报错的解决方法

你的核心问题是jsonb_exists_any的参数类型不匹配：该函数要求第二个参数是JSONB格式的数组，但你传入的是PostgreSQL原生整数数组，直接强制转换会失败。以下是几种无需修改字段格式（保留数字数组）的解决方案：

方案1：将原生整数数组转为JSONB数组 #

使用to_jsonb()函数把给定的整数数组转换成JSONB类型，和字段的格式完全匹配，即可正常调用jsonb_exists_any：

SELECT * FROM finishes
WHERE jsonb_exists_any(finishes.categories, to_jsonb(ARRAY[20, 23]::int[]));

to_jsonb()会自动把PostgreSQL的int[]转换成对应的JSONB数字数组（比如ARRAY[20,23]转成[20,23]::jsonb），完美适配字段类型。

方案2：使用?|操作符简化判断 #

PostgreSQL的JSONB提供了?|操作符，专门用于检查JSONB数组是否包含给定字符串列表中的任意元素。虽然操作符要求右侧是字符串数组，但JSONB中的数字会和对应的字符串形式匹配：

SELECT * FROM finishes
WHERE finishes.categories ?| ARRAY['20', '23'];

这种写法更简洁，性能和jsonb_exists_any基本一致。

方案3：展开数组后匹配（适合复杂场景） #

如果需要更灵活的过滤逻辑，可以用jsonb_array_elements把JSONB数组展开为行，再通过ANY判断交集：

SELECT * FROM finishes
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(finishes.categories) AS elem
    WHERE elem::integer = ANY(ARRAY[20, 23])
);

这种方法适合需要对数组元素做额外处理（比如范围判断）的场景，缺点是大表上的性能不如前两种方法（如果没有合适的索引）。

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