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?

You mean data->'identification'->>'registration_number'. -> for object, ->> for text value. — teppic
– teppic, Commented Nov 15, 2017 at 22:16
@teppic yes, that worked, thank you! (if you'd like to submit this as an answer please do) — Richard
– Richard, Commented Nov 15, 2017 at 22:43

teppic · Accepted Answer · 2017-11-16 01:22:17Z

4

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.

answered Nov 16, 2017 at 1:22

teppic

7,3161 gold badge34 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nildowjunior · Accepted Answer · 2018-09-20 14:48:55Z

3

Since version 9.3 Postgres has the #> and #>> operators. This operators allow the user to specify a path (using an array of text) inside jsonb column to get the value.

You could use this operator to achieve your goal in a simpler way.

CREATE INDEX ON mytable((data #>> '{identification, registration_number}'));

answered Sep 20, 2018 at 14:48

nildowjunior

311 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related