From: | Erwin Brandstetter <brsaweda(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Variable substitution in jsonb functions fails for jsonpath operator like_regex |
Date: | 2023-10-18 19:07:34 |
Message-ID: | CAGHENJ4A8awD2uXHkf50eV+9wyiDm3eP+G10J09+0VRmXBQAdw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The functions jsonb_path_exists() and friends accept a "vars" parameter for
parameter substitution in the jsonpath argument. This seems to work for all
jsonpath operators except "like_regex":
SELECT * FROM tbl
WHERE jsonb_path_exists(data, '$[*].value ? (@ like_regex $foo)', '{"foo":
"CEO"}');
> ERROR: syntax error at or near "$foo" of jsonpath input
> LINE 3: WHERE jsonb_path_exists(data, '$[*].value ? (@ like_regex $...
Notably, the same works even for "starts with":
SELECT * FROM tbl
WHERE jsonb_path_exists(data, '$[*].value ? (@ starts with $foo)',
'{"foo": "CEO"}');
I also tested related jsonb functions including jsonb_path_query(),
jsonb_path_query_first(), jsonb_path_query(). Always the same error message.
Here is the question on stackoverflow.com that brought the issue to my
attention (plus my answer with more details):
https://stackoverflow.com/questions/77317468/variable-substitution-for-postgres-jsonpath-operator-like-regex/77318568
Here is a related fiddle to play with:
https://dbfiddle.uk/4yRjIYlh
Tested with Postgres 16.0.
Regards
Erwin
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2023-10-18 20:06:48 | Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. |
Previous Message | PG Bug reporting form | 2023-10-18 17:43:08 | BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. |