Re:

On Tue, Feb 1, 2022 at 12:42 AM Дмитрий Воронин <carriingfate92(at)yandex(dot)ru>
wrote:

> Hi all,
>
> I'm using PostgreSQL 13.
>
> I have a table:
>
> CREATE TABLE test(docid integer, jsonb attrs);
>
> So, attrs contains data like
>
> ...
> "dates": ["2019-10-02", "2018-02-03"]
> ...
>
> So, I want to SELECT all docids, which dates in range:
>
> SELECT attrs FROM document_resinfo WHERE attrs @? '$.dates[*].datetime() ?
> (@ >= "2020-10-02".datetime())';
>
> How can I create index on attrs field to query docids with other date?
> Thanks.
>
>

Have you tried just putting a default index on that column? I think it
should work fine.

CREATE INDEX attrs_idx ON test (attrs)

IIRC, jsonb can be indexed like any other column and you get significant
performance benefits when using the index. Also IIRC, you can index
"deeper" into jsonb if you only want to index part of the jsonb structure -
which is more efficient, so you don't index a bunch of elements that you
never search.

Have you tried this approach? What problems are you experiencing?

Steve

>