Re: JSON Path and GIN Questions

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?

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?

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

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?

Best,

David

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