180

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.

2
  • 12
    "Where's the question?" - IN the title
    – rlib
    Jul 23, 2013 at 11:01
  • 3
    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. Jul 23, 2013 at 11:42

1 Answer 1

305

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.

13
  • 47
    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. Oct 22, 2013 at 20:53
  • 50
    The double-parentheses are also important.
    – Ron
    May 27, 2014 at 20:59
  • 17
    @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).
    – jpmc26
    Oct 6, 2015 at 19:44
  • 6
    Works also for unique indexes: CREATE UNIQUE INDEX ON publishers((info->>'name'));
    – maicher
    Nov 26, 2015 at 12:03
  • 28
    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.
    – Corey Cole
    Oct 2, 2018 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.