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

From Erik Wienhold
Subject Re: JSON Path and GIN Questions
Date
Msg-id 133696180.303713.1694566807910@office.mailbox.org
Whole thread Raw
In response to JSON Path and GIN Questions  ("David E. Wheeler" <david@justatheory.com>)
Responses Re: JSON Path and GIN Questions
Re: JSON Path and GIN Questions
Re: JSON Path and GIN Questions
Re: JSON Path and GIN Questions
List pgsql-hackers
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.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



pgsql-hackers by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Re: Avoid a possible null pointer (src/backend/utils/adt/pg_locale.c)
Next
From: ywgrit
Date:
Subject: Is the member name of hashctl inappropriate?