Re: JSON Path and GIN Questions

On 2023-10-09 01:13 +0200, David E. Wheeler write:
> On Sep 12, 2023, at 21:00, Erik Wienhold <ewie(at)ewie(dot)name> wrote:
>
> >> I posted this question on Stack Overflow (https://stackoverflow.com/q/77046554/79202)
> >> and from the suggestion I got there, it seems that @@ expects a boolean to be
> >> returned by the path query, while @? wraps it in an implicit exists(). Is that
> >> right?
> >
> > That's also my understanding. We had a discussion about the docs on @@, @?, and
> > jsonb_path_query on -general a while back [1]. Maybe it's useful also.
>
> Hi, finally getting back to this, still fiddling to figure out the
> differences. From the thread you reference [1], is the point that @@
> and jsonb_path_match() can only be properly used with a JSON Path
> expression that’s a predicate check?

I think so. That's also supported by the existing docs which only
mention "JSON path predicate" for @@ and jsonb_path_match().

> If so, as far as I can tell, only exists() around the entire path
> query, or the deviation from the SQL standard that allows an
> expression to be a predicate?

Looks like that. But note that exists() is also a filter expression.
So wrapping the entire jsonpath in exists() is also a deviation from the
SQL standard which only allows predicates in filter expressions, i.e.
'<path> ? (<predicate>)'.

> This suggest to me that the "Only the first item of the result is
> taken into account” bit from the docs may not be quite right.

Yes, this was also the issue in the referenced thread[1]. I think my
suggesstion in [2] explains it (as far as I understand it).

> Consider this example:
>
> david=# select jsonb_path_query('{"a":[false,true,false]}', '$.a ?(@[*] == false)');
> jsonb_path_query
> ------------------
> false
> false
> (2 rows)
>
> david=# select jsonb_path_match('{"a":[false,true,false]}', '$.a ?(@[*] == false)');
> ERROR: single boolean result is expected
>
> jsonb_path_match(), it turns out, only wants a single result. But
> furthermore perhaps the use of a filter predicate rather than a
> predicate expression for the entire path query is an error?

Yes, I think @@ and jsonb_path_match() should not be used with filter
expressions because the jsonpath returns whatever the path expression
yields (which may be an actual boolean value in the jsonb). The filter
expression only filters (as the name suggests) what the path expression
yields.

> Curiously, @@ seems okay with it:
>
> david=# select '{"a":[false,true,false]}'@@ '$.a ?(@[*] == false)';
> ?column?
> ----------
> t
>
> Not a predicate query, and somehow returns true even though the first
> item of the result is false? Is that how it should be?

Your example does a text search equivalent to:

select to_tsvector('{"a":[false,true,false]}') @@ plainto_tsquery('$.a ? (@[*] == true)')

You forgot the cast to jsonb. jsonb @@ jsonpath actually returns null:

test=# select '{"a":[false,true,false]}'::jsonb @@ '$.a ? (@[*] == false)';
?column?
----------
<null>
(1 row)

This matches the note right after the docs for @@:

"The jsonpath operators @? and @@ suppress the following errors: missing
object field or array element, unexpected JSON item type, datetime and
numeric errors. The jsonpath-related functions described below can also
be told to suppress these types of errors. This behavior might be
helpful when searching JSON document collections of varying structure."

That would be the silent argument of jsonb_path_match():

test=# select jsonb_path_match('{"a":[false,true,false]}', '$.a ? (@[*] == false)', silent => true);
jsonb_path_match
------------------
<null>
(1 row)

[1] https://www.postgresql.org/message-id/CACJufxE01sxgvtG4QEvRZPzs_roggsZeVvBSGpjM5tzE5hMCLA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/880194083.579916.1680598906819%40office.mailbox.org

--
Erik