Re: JSON Path and GIN Questions

From: Erik Rijkers <er(at)xs4all(dot)nl>
To: Erik Wienhold <ewie(at)ewie(dot)name>, "David E(dot) Wheeler" <david(at)justatheory(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: JSON Path and GIN Questions
Date: 2023-09-13 05:11:52
Message-ID: 640942d7-c996-0562-62c9-d69a4c1f9b1e@xs4all.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Op 9/13/23 om 03:00 schreef Erik Wienhold:
> Hi David,
>
> On 13/09/2023 02:16 CEST David E. Wheeler <david(at)justatheory(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2023-09-13 05:14:07 Re: Row pattern recognition
Previous Message Amit Kapila 2023-09-13 04:44:43 Re: subscription TAP test has unused $result