Variable substitution in jsonb functions fails for jsonpath operator like_regex

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 $...

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.

regards, tom lane

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

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 $...

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

Jeff Janes <jeff.janes@gmail.com> writes:

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

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).

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.

Oh, duh, makeItemLikeRegex() is expecting a plain JsonPathString,
and JsonPathParseItem.value.like_regex is set up for just a
constant pattern. We could no doubt fix that, but it's a bigger
lift than I was hoping.

Maybe the problem is that the regex pattern is compiled at the same time
the jsonpath is compiled?

Doesn't look that way to me: executeLikeRegex is just using
RE_compile_and_execute every time. (It's "caching" a text datum
representing the pattern string, which might be a good candidate for
the silliest use of caching I've ever seen in PG; it's surely not
buying any useful increment of performance.)

regards, tom lane

On Wed, Oct 18, 2023, 18:47 Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jeff Janes <jeff.janes@gmail.com> writes:

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

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).

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.

Oh, duh, makeItemLikeRegex() is expecting a plain JsonPathString,
and JsonPathParseItem.value.like_regex is set up for just a
constant pattern. We could no doubt fix that, but it's a bigger
lift than I was hoping.

Not in a position to do it myself but we should confirm we aren't simply
following the standard here, and if so decide whether we want to deviate.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wed, Oct 18, 2023, 18:47 Tom Lane <tgl@sss.pgh.pa.us> wrote:

Oh, duh, makeItemLikeRegex() is expecting a plain JsonPathString,
and JsonPathParseItem.value.like_regex is set up for just a
constant pattern. We could no doubt fix that, but it's a bigger
lift than I was hoping.

Not in a position to do it myself but we should confirm we aren't simply
following the standard here, and if so decide whether we want to deviate.

Hmm ... looks like we *are* following the standard:

<JSON like_regex predicate> ::=
<JSON path wff> like_regex <JSON like_regex pattern>
[ flag <JSON like_regex flags> ]
<JSON like_regex pattern> ::=
<JSON path string literal>
<JSON like_regex flag> ::=
<JSON path string literal>

whereas "starts with" has

<JSON starts with predicate> ::=
<JSON starts with whole> starts with <JSON starts with initial>
<JSON starts with whole> ::=
<JSON path wff>
<JSON starts with initial> ::=
<JSON path wff>

The text mentions that "the second operand is permitted to be an SQL/JSON
sequence and to support existential semantics", whereas they evidently
don't want that for a regex pattern.

regards, tom lane

On Thu, 19 Oct 2023 at 21:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wed, Oct 18, 2023, 18:47 Tom Lane <tgl@sss.pgh.pa.us> wrote:

Oh, duh, makeItemLikeRegex() is expecting a plain JsonPathString,
and JsonPathParseItem.value.like_regex is set up for just a
constant pattern. We could no doubt fix that, but it's a bigger
lift than I was hoping.

Not in a position to do it myself but we should confirm we aren't simply
following the standard here, and if so decide whether we want to deviate.

Hmm ... looks like we *are* following the standard:

<JSON like_regex predicate> ::=
<JSON path wff> like_regex <JSON like_regex pattern>
[ flag <JSON like_regex flags> ]
<JSON like_regex pattern> ::=
<JSON path string literal>
<JSON like_regex flag> ::=
<JSON path string literal>

whereas "starts with" has

<JSON starts with predicate> ::=
<JSON starts with whole> starts with <JSON starts with initial>
<JSON starts with whole> ::=
<JSON path wff>
<JSON starts with initial> ::=
<JSON path wff>

The text mentions that "the second operand is permitted to be an SQL/JSON
sequence and to support existential semantics", whereas they evidently
don't want that for a regex pattern.

So input from "vars" cannot be substituted into the jsonpath expression
after "like_regex" (as opposed to all other jsonpath operators). Seems
pretty random from a user's perspective.

Be that as it may, if that's the consensus, I'll have a closer look at the
manual page to try and convey the rules.
Do we have an accord?

Regards
Erwin

Erwin Brandstetter <brsaweda@gmail.com> writes:

On Thu, 19 Oct 2023 at 21:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Hmm ... looks like we *are* following the standard:
...
The text mentions that "the second operand is permitted to be an SQL/JSON
sequence and to support existential semantics", whereas they evidently
don't want that for a regex pattern.

So input from "vars" cannot be substituted into the jsonpath expression
after "like_regex" (as opposed to all other jsonpath operators). Seems
pretty random from a user's perspective.

I agree it looks pretty random if you haven't drilled down into the
spec's fine print. Personally I wouldn't be opposed to extending
the spec here (not that I'm volunteering to write the patch).

Nosing around in jsonpath_gram.y, I see datetime_template as the
only other place where there's a random-seeming choice to allow
STRING_P but not VARIABLE_P. Should we tackle that too?

regards, tom lane