Re: Is it possible to index "deep" into a JSONB column? - Mailing list pgsql-general

Hi Bryn,

On Mon, 30 May 2022 at 03:12, Bryn Llewellyn <bryn@yugabyte.com> wrote:
...
>
> Try this:
>
>    snapshot -> ‘employee’->>’date_of_birth’
>

Syntactically, that works:

    create index bryn on paiyroll_payrun using btree ((snapshot ->
'employee'->>'date_of_birth'));

But IIUC it is looking for 'date_of_birth' in the wrong level. it
would need to do something like this:

    create index bryn on paiyroll_payrun using btree ((snapshot ->
'employee'->'2209'->>'date_of_birth'));

To paraphrase, my question is about how to replace the '2209' with all
possible object keys. For the employee-is-an-array, the documentation
says this should work:

    create index bryn2 on paiyroll_payrun using btree ((snapshot ->
'employee[*]'->>'date_of_birth'));

but is silent on the employee-is-an-object case. (As I said, in case
it helps, in my case the keys are in fact stringified numbers).

Thanks, Shaheed