Well, I see what you are saying, but still, when checking the result of SELECT 'a' ~ '\$$' I'm getting a FALSE, so I expect the JSONPATH parser to work the same way.
Furthermore, when checking regex101.com with \$$ as the pattern and a as the value I'm getting no match.
PG Bug reporting form <noreply@postgresql.org> writes: > I've found an issue with the like_regex statement when using it together > with another && condition. > In the following example I'm expecting the result will end as: > [{"id":9,"value":"a"}] but instead, it ends with an empty array:
> WITH a(attributes) AS ( > SELECT '[{"id":9,"value":"a"},{"id":9,"value":"a$"}]'::jsonb > ) > SELECT jsonb_path_query_array(attributes,'$[*] ? (!(@.id==9 && @.value > like_regex "\$$"))') FROM a
I think you're short a backslash:
=# WITH a(attributes) AS ( SELECT '[{"id":9,"value":"a"},{"id":9,"value":"a$"}]'::jsonb ) SELECT jsonb_path_query_array(attributes,'$[*] ? (!(@.id==9 && @.value like_regex "\\$$"))') FROM a ; jsonb_path_query_array --------------------------- [{"id": 9, "value": "a"}] (1 row)
I believe one level of backslashing gets eaten by the jsonpath parser while parsing the literal, so your version ends as LIKE "$$" which is not different from LIKE "$" and will match every string.