Postgres: create index on attribute of attribute in JSONB column?

Question

I'm working in Postgres 9.6.5. I have the following table:

 id             | integer      
 data           | jsonb 

The data in the data column is nested, in the form:

{ 'identification': { 'registration_number': 'foo' }}

I'd like to index registration_number, so I can query on it. I've tried this (based on this answer):

CREATE INDEX ON mytable((data->>'identification'->>'registration_number'));

But got this:

ERROR:  operator does not exist: text ->> unknown
LINE 1: CREATE INDEX ON psc((data->>'identification'->>'registration...                                                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

What am I doing wrong?

Answer 1

You want:

CREATE INDEX ON mytable((data -> 'identification' ->> 'registration_number'));

The -> operator returns the jsonb object under the key, and the ->> operator returns the jsonb object under the key as text. The most notable difference between the two operators is that ->> will "unwrap" string values (i.e. remove double quotes from the TEXT representation).

The error you're seeing is reported because data ->> 'identification' returns text, and the subsequent ->> is not defined for the text type.