How to create index on JSON field in Postgres?

Question

In PostgreSQL 9.3 Beta 2 (?), how do I create an index on a JSON field? I tried it using the -> operator used for hstore but got the following error:

 CREATE TABLE publishers(id INT, info JSON);
 CREATE INDEX ON publishers((info->'name'));

ERROR: data type json has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type.

In future please take a look at stackoverflow.com/tags/postgresql/info, the "asking better questions" section; it might help get better answers sooner with fewer annoying questions. — Craig Ringer
– Craig Ringer, Commented Jul 23, 2013 at 11:42

sdgluck · Accepted Answer · 2020-02-26 16:11:19Z

329

Found:

CREATE TABLE publishers(id INT, info JSON); 
CREATE INDEX ON publishers((info->>'name'));

As stated in the comments, the subtle difference here is ->> instead of ->. The former one returns the value as text, the latter as a JSON object.

edited Feb 26, 2020 at 16:11

sdgluck

27.8k12 gold badges82 silver badges96 bronze badges

answered Jul 23, 2013 at 11:25

rlib

8,0473 gold badges37 silver badges43 bronze badges

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

13 Comments

Daniel Rikowski Over a year ago

Just in case you are looking for the difference: It is ->> instead of ->. The former one returns the value as text, the latter one returns a JSON object.

Ron Over a year ago

The double-parentheses are also important.

jpmc26 Over a year ago

@Jac_opo It extracts them as TEXT, though. If you want to do integer comparisons instead of string comparisons, you have to add a cast: ((info->>'name')::INT).

maicher Over a year ago

Works also for unique indexes: CREATE UNIQUE INDEX ON publishers((info->>'name'));

Corey Cole Over a year ago

If you want to create an index on a field inside a sub-object of your JSON column, thanks to @DanielRikowski I figured out I needed to do create index idx_name on table_name ((json_column->'child_obj'->>'child_obj_field')); We first need to use -> to get the JSON object and then ->> to get the child object value as text.

|

Collectives™ on Stack Overflow

How to create index on JSON field in Postgres?

1 Answer 1

13 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Linked

Related