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
