Date: Wed, 13 Sep 2023 03:00:07 +0200 (CEST)
From: Erik Wienhold <ewie@ewie.name>
To: "David E. Wheeler" <david@justatheory.com>,
	pgsql-hackers@lists.postgresql.org
Message-ID: <133696180.303713.1694566807910@office.mailbox.org>
In-Reply-To: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
Subject: Re: JSON Path and GIN Questions
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
Archived-At: <https://www.postgresql.org/message-id/133696180.303713.1694566807910%40office.mailbox.org>
Precedence: bulk

Hi David,

On 13/09/2023 02:16 CEST David E. Wheeler <david@justatheory.com> wrote:

> CREATE TABLE MOVIES (id SERIAL PRIMARY KEY, movie JSONB NOT NULL);
> \copy movies(movie) from PROGRAM 'curl -s https://raw.githubusercontent.c=
om/prust/wikipedia-movie-data/master/movies.json | jq -c ".[]" | sed "s|\\\=
\|\\\\\\\\|g"';
> create index on movies using gin (movie);
> analyze movies;
>
> I have been confused as to the difference between @@ vs @?: Why do these
> return different results?
>
> david=3D# select id from movies where movie @@ '$ ?(@.title =3D=3D "New L=
ife Rescue")';
>  id
> ----
> (0 rows)
>
> david=3D# select id from movies where movie @? '$ ?(@.title =3D=3D "New L=
ife Rescue")';
>  id
> ----
>  10
> (1 row)
>
> I posted this question on Stack Overflow (https://stackoverflow.com/q/770=
46554/79202),
> and from the suggestion I got there, it seems that @@ expects a boolean t=
o 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.

> If so, I=E2=80=99d like to submit a patch to the docs talking about this,=
 and
> suggesting the use of jsonb_path_query() to test paths to see if they ret=
urn
> a boolean or not.

+1

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

--
Erik