Re: JSON Path and GIN Questions - Mailing list pgsql-hackers

From David E. Wheeler
Subject Re: JSON Path and GIN Questions
Date
Msg-id 0E191A5E-8F6D-403E-838F-53F4AA69AADA@justatheory.com
Whole thread Raw
In response to Re: JSON Path and GIN Questions  (Erik Wienhold <ewie@ewie.name>)
Responses Re: JSON Path and GIN Questions
List pgsql-hackers
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
thepoint 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
allowsan 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
quiteright. 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
ratherthan 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
shouldbe? 

Best,

David

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



pgsql-hackers by date:

Previous
From: Peter Smith
Date:
Subject: Re: Synchronizing slots from primary to standby
Next
From: Thomas Munro
Date:
Subject: Re: CREATE DATABASE with filesystem cloning