Thread: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
From
sulfinu@gmail.com
Date:
Hello,
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:
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:
- it's my (calculated) risk;
- 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.
Re: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
From
"David G. Johnston"
Date:
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.
Re: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
From
Tom Lane
Date:
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%40mail.gmail.com
Re: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
From
sulfinu@gmail.com
Date:
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.