Re: JSON Path and GIN Questions

On Sep 12, 2023, at 21:00, Erik Wienhold <ewie@ewie.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