Is it possible to index "deep" into a JSONB column?

Поиск
Список
Период
Сортировка
От Shaheed Haque
Тема Is it possible to index "deep" into a JSONB column?
Дата
Msg-id CAHAc2jdiRtw3qus_rvz1QvcxUJ9AgaBKObP-Fvx1q6Vr80n_xw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: JSONB index not in use, but is TOAST the real cause of slow query?  (Shaheed Haque <shaheedhaque@gmail.com>)
Ответы Re: Is it possible to index "deep" into a JSONB column?  (Bryn Llewellyn <bryn@yugabyte.com>)
Re: Is it possible to index "deep" into a JSONB column?  (Imre Samu <pella.samu@gmail.com>)
Список pgsql-general
Suppose I have a JSONB field called "snapshot". I can create a GIN
index on it like this:

  create index idx1 on mytable using gin (snapshot);

In principle, I believe this allows index-assisted access to keys and
values nested in arrays and inner objects but in practice, it seems
the planner "often" decides to ignore the index in favour of a table
scan. (As discussed elsewhere, this is influenced by the number of
rows, and possibly other criteria too).

Now, I know it is possible to index inner objects, so that is snapshot
looks like this:

{
    "stuff": {},
    "more other stuff": {},
    "employee": {
         "1234": {"date_of_birth": "1970-01-01"},
         "56B789": {"date_of_birth": "1971-02-02"},
    }
}

I can say:

  create index idx2 on mytable using gin ((snapshot -> 'employee'));

But what is the syntax to index only on date_of_birth? I assume a
btree would work since it is a primitive value, but WHAT GOES HERE in
this:

  create index idx3 on mytable using btree ((snapshot ->'employee' ->
WHAT GOES HERE -> 'date_of_birth'));

I believe an asterisk "*" would work if 'employee' was an array, but
here it is  nested object with keys. If it helps, the keys are
invariably numbers (in quoted string form, as per JSON).

Thanks, Shaheed



В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: psql 15beta1 does not print notices on the console until transaction completes
Следующее
От: Alastair McKinley
Дата:
Сообщение: Re: psql 15beta1 does not print notices on the console until transaction completes