The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
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.
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.
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]/messages/by-id/CAGHENJ4A8awD2uXHkf50eV+9wyiDm3eP+G10J09+0VRmXBQAdw@mail.gmail.com. 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]: /messages/by-id/CAGHENJ4A8awD2uXHkf50eV+9wyiDm3eP+G10J09+0VRmXBQAdw@mail.gmail.com
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:
Show quoted text
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.