In a system I messed up a migration of a simple varchar column to a jsonb column. I now have json strings in the jsonb column with the string from the varchar column surrounded by double quotes, square brackets and backslashes that actually should have been a json array containing only the original string. An example of such a messed up value is "[\"\\\"Foo\\\"\"]".
But I also have a few other values already in the jsonb column that are correct.
I wanted to select the columns containing the messed up json. Therefore I wrote the following sql:
select * from foo where bar_json::text like '"[\"\\\"%\\\"\"]"'
However I noticed I didn’t get any values back from the query.
When trying the following:
select '"[\"\\\"Foo\\\"\"]"' like '"[\"\\\"%\\\"\"]"'
this always yields false. Why is that the case?
I’m using PostgreSQL 16.3, compiled by Visual C++ build 1938, 64-bit.
>Solution :
The backslash \ is by default an escape character in LIKE patterns. You can disable that:
SELECT * FROM foo
WHERE bar_json::text LIKE '"[\"\\\"%\\\"\"]"' ESCAPE '';