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