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

> shaheedhaque(at)gmail(dot)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).

Try this:

snapshot -> ‘employee’->>’date_of_birth’