Variable substitution in jsonb functions fails for jsonpath operator like_regex

Поиск
Список
Период
Сортировка
От Erwin Brandstetter
Тема Variable substitution in jsonb functions fails for jsonpath operator like_regex
Дата
Msg-id CAGHENJ4A8awD2uXHkf50eV+9wyiDm3eP+G10J09+0VRmXBQAdw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Variable substitution in jsonb functions fails for jsonpath operator like_regex
Список 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):

Here is a related fiddle to play with:

Tested with Postgres 16.0.

Regards
Erwin

В списке pgsql-bugs по дате отправления: