Op 9/13/23 om 03:00 schreef Erik Wienhold:
Hi David,
On 13/09/2023 02:16 CEST David E. Wheeler <da...@justatheory.com> 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.gmail.com
--
Erik
"All use of json*() functions preclude index usage."
That sentence is missing from the documentation.
Erik Rijkers
