Erwin Brandstetter <brsaweda@gmail.com> writes: > 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 $...
Maybe it wouldn't take more than s/STRING_P/starts_with_initial/ here (though I'd then rename starts_with_initial to something more generic). With bad luck there might be some dependency on this restriction downstream of the grammar, but I suspect not. Didn't try though.
I tried it, and it didn't work. No error, it just doesn't match anything--including literal values which do match things in HEAD.
Maybe the problem is that the regex pattern is compiled at the same time the jsonpath is compiled? Then it can't just have a different pattern slotted in later through a variable.
For example, this finds the row in HEAD but not with the proposed change:
with tbl as (select '[{"value":"CEO"}]'::jsonb data) SELECT * FROM tbl WHERE jsonb_path_exists(data, '$[*].value ? (@ like_regex "CEO")');
