pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedThe jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
4+ messages / 3 participants
[nested] [flat]
* The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
@ 2024-08-06 16:42 sulfinu@gmail.com
0 siblings, 2 replies; 4+ messages in thread
From: sulfinu@gmail.com @ 2024-08-06 16:42 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
this one is noticed on PostgreSQL 16.3.
One of the predicates that can be used in jsonpath expressions
<file:///usr/share/doc/postgresql-16.3-r2/html/functions-json.html#FUNCTIONS-SQLJSON-FILTER-EX-TABLE>
is like_regex, which unfortunately does not accept variables for pattern or
flags:
SELECT jsonb_path_match('{"name":"abc"}'::jsonb, '$.name like_regex $p flag
"q"', '{"p": "abc"}'::jsonb, true);
SELECT jsonb_path_match('{"name":"abc"}'::jsonb, '$.name like_regex "abc"
flag $f', '{"p": "abc", "f": "q"}'::jsonb, true);
The starts with predicate on the other hand accepts variables for prefix
(but not for the tested string):
SELECT jsonb_path_match('{"name":"abc"}'::jsonb, '$.name starts with
$p', '{"p":
"abc"}'::jsonb, true);
I would like to be able to use a variable inside the like_regex predicate,
at least for the pattern. I need that since I generate dynamically the
involved predicates and this one is a containment test, actually.
If someone worries about self-shooting in the foot, I have two answers to
that:
1. it's *my* (calculated) risk;
2. I can already shoot myself in the foot with the plain SQL regular
expression test - see
`SELECT regexp_like('abc', $1, $2);` with parameters ["abc", "q"]
or
`SELECT 'abc' ~ $1` with parameters ["abc"]
Thank you.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
@ 2024-08-06 17:48 David G. Johnston <david.g.johnston@gmail.com>
parent: sulfinu@gmail.com
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2024-08-06 17:48 UTC (permalink / raw)
To: sulfinu@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
On Tue, Aug 6, 2024 at 10:28 AM <sulfinu@gmail.com> wrote:
> this one is noticed on PostgreSQL 16.3.
> One of the predicates that can be used in jsonpath expressions is
> like_regex, which unfortunately does not accept variables for pattern or
> flags:
>
> I would like to be able to use a variable inside the like_regex
> predicate, at least for the pattern.
>
>
You can use a format function to build it dynamically. Unfortunately it is
a bit of a pain since you need to do escaping; which is a pain for regex.
SQL scope doesn't have this problem so moving your logic outside of a json
is should seriously be considered before trying to construct dynamic
jsonpath expressions.
I get the impression we are conforming to a standard here so even proposing
a patch to change this behavior would require some convincing to deviate
from the standard on this point. Though I could see adding a new format
escape and related quote_jsonpathliteral function to be something we'd be
more open to in order to make dynamic json path expressions more easily
doable.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
@ 2024-08-06 17:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: sulfinu@gmail.com
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2024-08-06 17:50 UTC (permalink / raw)
To: sulfinu@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
sulfinu@gmail.com writes:
> I would like to be able to use a variable inside the like_regex predicate,
> at least for the pattern.
This was discussed before [1]. The restriction to a constant pattern
is per SQL spec. It's not entirely clear to us why the spec is
written that way, but there may be some actual semantic point behind
it. In any case, if you want to propose a patch, that thread would
probably be the best place to do it.
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/CAGHENJ4A8awD2uXHkf50eV%2B9wyiDm3eP%2BG10J09%2B0VRmXBQAdw...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
@ 2024-08-06 20:19 sulfinu@gmail.com
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: sulfinu@gmail.com @ 2024-08-06 20:19 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Looks like I didn't make myself undestood: I do *not* produce the regular
expression dynamically (on a side note, I would have to be careful to
escape the right stuff, regardless of SQL or jsonpath). It is the WHERE
clause of the query that I build dynamically. That's why I would like the
pattern to be submitted as a variable, (which I anyway employ with a "q"
flag).
*Question:* is there another way to express the *contains* predicate in
jsonpath?
În mar., 6 aug. 2024 la 20:49, David G. Johnston <david.g.johnston@gmail.com>
a scris:
> You can use a format function to build it dynamically. Unfortunately it
> is a bit of a pain since you need to do escaping; which is a pain for
> regex. SQL scope doesn't have this problem so moving your logic outside of
> a json is should seriously be considered before trying to construct dynamic
> jsonpath expressions.
>
> I get the impression we are conforming to a standard here so even
> proposing a patch to change this behavior would require some convincing to
> deviate from the standard on this point. Though I could see adding a new
> format escape and related quote_jsonpathliteral function to be something
> we'd be more open to in order to make dynamic json path expressions more
> easily doable.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-08-06 20:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-06 16:42 The jsonpath predicate `like_regex` does not accept variables for pattern (or flags) sulfinu@gmail.com
2024-08-06 17:48 ` David G. Johnston <david.g.johnston@gmail.com>
2024-08-06 20:19 ` sulfinu@gmail.com
2024-08-06 17:50 ` 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