Thread

1. The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)

   sulfinu@gmail.com — 2024-08-06T16:42:09Z

   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.
   

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

   David G. Johnston <david.g.johnston@gmail.com> — 2024-08-06T17:48:56Z

   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.
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-08-06T17:50:29Z

   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
   
   
   
   

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

   sulfinu@gmail.com — 2024-08-06T20:19:15Z

   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.
   >
   >