Re: JSON Path and GIN Questions

On Sep 12, 2023, at 21:00, Erik Wienhold <ewie@ewie.name> wrote:

> 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.

Okay, I’ll take a pass at expanding the docs on this. I think a little mini-tutorial on these two operators would be useful.

Meanwhile, I’d like to re-up this question about the index qualification of non-equality JSON Path operators.

On Sep 12, 2023, at 20:16, David E. Wheeler <david@justatheory.com> wrote:

> Issue 3: Index Use for Comparison
> ---------------------------------
> 
> From the docs (https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING), I had assumed any JSON Path query would be able to use the GIN index. However while the use of the == JSON Path operator is able to take advantage of the GIN index, apparently the >= operator cannot:
> 
> david=# explain analyze select id from movies where movie @? '$ ?($.year >= 2023)';
>                                               QUERY PLAN                                      ---------------------------------------------------------------------------------------------------------
> Seq Scan on movies  (cost=0.00..3741.41 rows=366 width=4) (actual time=34.815..36.259 rows=192 loops=1)
>   Filter: (movie @? '$?($."year" >= 2023)'::jsonpath)
>   Rows Removed by Filter: 36081
> Planning Time: 1.864 ms
> Execution Time: 36.338 ms
> (5 rows)
> 
> Is this expected? Originally I tried with json_path_ops, which I can understand not working, since it stores hashes of paths, which would allow only exact matches. But a plain old GIN index doesn’t appear to work, either. Should it? Is there perhaps some other op class that would allow it to work? Or would I have to create a separate BTREE index on `movie -> 'year'`?

Thanks,

David