Re: Variable substitution in jsonb functions fails for jsonpath operator like_regex

On Wed, Oct 18, 2023 at 5:20 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Erwin Brandstetter <brsaweda(at)gmail(dot)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 $...
>
> > Notably, the same works even for "starts with":
>
>
> Hmm, maybe just an oversight in jsonpath_gram.y?
>
> predicate:
> ...
> | expr STARTS_P WITH_P starts_with_initial
> | expr LIKE_REGEX_P STRING_P
> | expr LIKE_REGEX_P STRING_P FLAG_P STRING_P
> ;
>
> starts_with_initial:
> STRING_P { $$ = makeItemString(&$1); }
> | VARIABLE_P { $$ = makeItemVariable(&$1); }
> ;
>
> 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")');

Cheers,

Jeff