Re: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)

On Tue, Aug 6, 2024 at 10:28 AM <sulfinu(at)gmail(dot)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.