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

From: sulfinu(at)gmail(dot)com
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
Date: 2024-08-06 16:42:09
Message-ID: CAGH1kmz2QT+m2Lr8ts=YWDq+435JWZ2MPFE2WpS2yFh2HQrQ5g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2024-08-06 17:48:56 Re: The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
Previous Message p2wap3 2024-08-06 15:31:08 Re: BUG #18570: Drop event trigger for DDL finishes successfully but trigger still executed on DDL