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