public inbox for [email protected]
help / color / mirror / Atom feedFrom: Erik Wienhold <[email protected]>
To: David E. Wheeler <[email protected]>
To: [email protected]
Subject: Re: JSON Path and GIN Questions
Date: Wed, 13 Sep 2023 03:00:07 +0200 (CEST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
Hi David,
On 13/09/2023 02:16 CEST David E. Wheeler <[email protected]> wrote:
> CREATE TABLE MOVIES (id SERIAL PRIMARY KEY, movie JSONB NOT NULL);
> \copy movies(movie) from PROGRAM 'curl -s https://raw.githubusercontent.com/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=# select id from movies where movie @@ '$ ?(@.title == "New Life Rescue")';
> id
> ----
> (0 rows)
>
> david=# select id from movies where movie @? '$ ?(@.title == "New Life Rescue")';
> id
> ----
> 10
> (1 row)
>
> 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.
> If so, I’d 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 return
> a boolean or not.
+1
[1] https://www.postgresql.org/message-id/CACJufxE01sxgvtG4QEvRZPzs_roggsZeVvBSGpjM5tzE5hMCLA%40mail.gma...
--
Erik
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: JSON Path and GIN Questions
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox