pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17660: JSONPATH issue using like_regex followed by the && operator
4+ messages / 3 participants
[nested] [flat]
* BUG #17660: JSONPATH issue using like_regex followed by the && operator
@ 2022-10-22 11:55 PG Bug reporting form <noreply@postgresql.org>
2022-10-22 13:48 ` Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2022-10-22 11:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: shahar@cybear.co
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator
2022-10-22 11:55 BUG #17660: JSONPATH issue using like_regex followed by the && operator PG Bug reporting form <noreply@postgresql.org>
@ 2022-10-22 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-10-23 07:56 ` Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator Shahar Belizon <shahar@cybear.co>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2022-10-22 13:48 UTC (permalink / raw)
To: shahar@cybear.co; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator
2022-10-22 11:55 BUG #17660: JSONPATH issue using like_regex followed by the && operator PG Bug reporting form <noreply@postgresql.org>
2022-10-22 13:48 ` Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-10-23 07:56 ` Shahar Belizon <shahar@cybear.co>
2022-10-23 15:31 ` Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Shahar Belizon @ 2022-10-23 07:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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:
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator
2022-10-22 11:55 BUG #17660: JSONPATH issue using like_regex followed by the && operator PG Bug reporting form <noreply@postgresql.org>
2022-10-22 13:48 ` Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator Tom Lane <tgl@sss.pgh.pa.us>
2022-10-23 07:56 ` Re: BUG #17660: JSONPATH issue using like_regex followed by the && operator Shahar Belizon <shahar@cybear.co>
@ 2022-10-23 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-10-23 15:31 UTC (permalink / raw)
To: Shahar Belizon <shahar@cybear.co>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-10-23 15:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-22 11:55 BUG #17660: JSONPATH issue using like_regex followed by the && operator PG Bug reporting form <noreply@postgresql.org>
2022-10-22 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-10-23 07:56 ` Shahar Belizon <shahar@cybear.co>
2022-10-23 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox