Обсуждение: BUG #17660: JSONPATH issue using like_regex followed by the && operator

The following bug has been logged on the website:

Bug reference:      17660
Logged by:          Shahar Belizon
Email address:      shahar@cybear.co
PostgreSQL version: 14.4
Operating system:   CentOS
Description:

Hello,
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

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

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