PostgreSQL jsonb_path_exists实现数值型数据的类LIKE查询

原查询使用jsonb_path_exists搭配like_regex能正常匹配JSON字符串类型字段，但无法匹配数值类型字段——比如要匹配id:123456，传入搜索关键词"123"时查不到结果。我们需要实现同时兼容字符串和数值的类LIKE模糊查询，且无需将整个JSONB字段转为文本。

解决思路 #

利用JSON路径表达式中的string()函数，将任意JSON值（无论是数值、字符串还是其他基础类型）转换为字符串形式，再执行正则匹配。这样就能统一处理不同类型的字段，实现跨类型的模糊搜索。

修正后的查询语句 #

select project
from search_table
where jsonb_path_exists(project, '$.** ? (string(@) like_regex "${searchString}" flag "i")');

完整测试验证 #

-- 创建测试表
CREATE TABLE IF NOT EXISTS dmspgdev.zz_yache_test
(
    project_id numeric(19,0),
    project jsonb
);

-- 插入测试数据
insert into dmspgdev.zz_yache_test
values (1,'{"id":123456, "data":"test1"}');
insert into dmspgdev.zz_yache_test
values (2,'{"id":789323, "data":"search works with string"}');

-- 测试数值匹配：返回id为123456的记录
select project
from dmspgdev.zz_yache_test
where jsonb_path_exists(project, '$.** ? (string(@) like_regex "456" flag "i")');

-- 测试字符串匹配：返回包含"search"的记录
select project
from dmspgdev.zz_yache_test
where jsonb_path_exists(project, '$.** ? (string(@) like_regex "search" flag "i")');

-- 测试跨类型匹配：同时匹配含"3"的数值和字符串
select project
from dmspgdev.zz_yache_test
where jsonb_path_exists(project, '$.** ? (string(@) like_regex "3" flag "i")');

优势说明 #

相比将整个JSONB转为文本查询的临时方案，这种方式更精准——不会误匹配JSON结构字符（比如大括号、冒号），同时如果JSONB字段建有GIN索引（如jsonb_path_ops类型），还能更好地利用索引优化查询性能。

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