Re: JSON Path and GIN Questions

On Sep 12, 2023, at 21:00, Erik Wienhold <ewie(at)ewie(dot)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(at)justatheory(dot)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