175

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.

Kerem
  • 11,377
  • 5
  • 59
  • 58
rlib
  • 7,444
  • 3
  • 32
  • 40
  • 11
    "Where's the question?" - IN the title – rlib Jul 23 '13 at 11:01
  • 3
    In future please take a look at http://stackoverflow.com/tags/postgresql/info, the "asking better questions" section; it might help get better answers sooner with fewer annoying questions. – Craig Ringer Jul 23 '13 at 11:42

1 Answers1

294

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.

sdgluck
  • 24,894
  • 8
  • 75
  • 90
rlib
  • 7,444
  • 3
  • 32
  • 40
  • 3
    [JSON Functions and Operators](http://www.postgresql.org/docs/9.3/static/functions-json.html) – Jared Beck Sep 11 '13 at 20:27
  • 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. – Daniel Rikowski Oct 22 '13 at 20:53
  • 49
    The double-parentheses are also important. – Ron May 27 '14 at 20:59
  • 2
    More generally this looks like an example of an expression-based index http://www.postgresql.org/docs/9.4/static/indexes-expressional.html. – MAJ Apr 28 '15 at 17:59
  • What is the correct way to do this if it is a int? not a string – BuddyJoe May 13 '15 at 15:01
  • @BuddyJoe the same, ->> extracts integers too – Jacopofar Jul 06 '15 at 09:56
  • 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 06 '15 at 19:44
  • 6
    Works also for unique indexes: `CREATE UNIQUE INDEX ON publishers((info->>'name'));` – maicher Nov 26 '15 at 12:03
  • 3
    If you want to create an index of the whole json statement (for example to make sure it is unique in your table), you can convert it to text in the index `CREATE UNIQUE INDEX ON myTable ((DATA::TEXT));` – some Jul 10 '16 at 12:48
  • 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 02 '18 at 00:22
  • What's the correct syntax to drop this index? – slim Jan 20 '21 at 16:56
  • one more option to create index for int: create index publishers_age on "publishers" (cast("info"->>'age' as int)) – Sam Sch Aug 12 '21 at 09:23
  • I am trying to learn about creating indices on jsonb. I have to run query to select 5 or 6 attributes from the jsonb column under same parent, on 20+ million row table. It is currently taking a couple of hours just to run the query. One of the comments above is `The double-parentheses are also important.`. Where can I find more details of why is that important and what happens if we don't use the double-parentheses above. Can one of the experts help with this - how can I use indexing to speed up the query? And some technical details on double-parentheses comment. Thanks – adbdkb Nov 13 '22 at 17:51