BUG #17660: JSONPATH issue using like_regex followed by the && operator

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.

regards, tom lane

Hello Tom, and thank you for the quick response.
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.

Best Regards,
Shahar

On Sat, 22 Oct 2022 at 16:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Shahar Belizon <shahar@cybear.co> writes:

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.

I don't think you did grasp the point. There's an additional level of
literal-parsing and backslash-stripping involved in the jsonpath case,
because the jsonpath path string contains an embedded string literal,
which has to have some kind of escaping behavior. Otherwise, how could
you search for a pattern that includes a double-quote?

The underlying regex behavior is the same either way; it's the surface
syntax that's different because of the extra parsing layer.

regards, tom lane