The jsonpath predicate `like_regex` does not accept variables for pattern (or flags)
sulfinu@gmail.com
From: sulfinu@gmail.com
To: pgsql-bugs@lists.postgresql.org
Date: 2024-08-06T16:42:09Z
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.