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

shaheedhaque@gmail.com wrote:

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