PostgreSQL中匹配错误jsonb格式的LIKE表达式为何返回false

根本原因：LIKE默认把反斜杠当转义符 #

PostgreSQL的LIKE操作符默认会将反斜杠（\）解析为转义字符，而非普通字符串内容。你编写的匹配模式"[\"\\\"%\\\"\"]"里，大量反斜杠会被LIKE当作转义标记处理，导致实际生效的匹配规则和你预期的完全不一致，自然查不到结果。

举个例子，你测试语句里的模式"[\"\\\"%\\\"\"]"，经过LIKE的转义解析后，实际变成了：

• 开头是"
• 接着是[
• 然后是"（因为\\\"被解析成单个"）
• 然后是通配符%
• 再是"（同样\\\"解析成"）
• 最后是]"

但你的目标字符串"[\"\\\"Foo\\\"\"]"里实际包含多个反斜杠，和解析后的模式完全不匹配，所以返回false。

解决办法 #

1. 给LIKE指定自定义转义符 #

在LIKE语句末尾添加ESCAPE子句，选择一个不会出现在目标字符串中的字符（比如#）作为转义符，这样反斜杠就会被当作普通字符处理：

-- 测试语句会返回true
select '"[\"\\\"Foo\\\"\"]"' like '"[\"\\\"%\\\"\"]"' escape '#';

-- 业务查询修改为
select * from foo where bar_json::text like '"[\"\\\"%\\\"\"]"' escape '#';

2. 使用正则表达式匹配 #

如果需要更灵活的匹配逻辑，可以用PostgreSQL的正则表达式操作符~，注意正则里的反斜杠需要额外转义一次：

-- 测试语句
select '"[\"\\\"Foo\\\"\"]"' ~ '^\["\\"%"\\"\]$';

-- 业务查询
select * from foo where bar_json::text ~ '^\["\\"%"\\"\]$';

3. 直接利用jsonb特性判断（推荐） #

既然是jsonb列，没必要转成text硬匹配。错误值本质是把数组又套成了字符串类型的jsonb，你可以用jsonb_typeof直接判断类型：

select * from foo 
where jsonb_typeof(bar_json) = 'string'  -- 正确的数组类型应为'array'
and bar_json::text like '%"[%]"%' escape '#';

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