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