4

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?

Richard
  • 62,943
  • 126
  • 334
  • 542
  • 2
    You mean `data->'identification'->>'registration_number'`. `->` for object, `->>` for text value. – teppic Nov 15 '17 at 22:16
  • @teppic yes, that worked, thank you! (if you'd like to submit this as an answer please do) – Richard Nov 15 '17 at 22:43

2 Answers2

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.

teppic
  • 7,051
  • 1
  • 29
  • 35
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}'));
nildowjunior
  • 31
  • 1