How to create index on JSON field in Postgres?

json postgresql indexing postgresql-9.3
rlib picture rlib · Jul 23, 2013 · Viewed 55.4k times · Source

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.

Answer

rlib picture rlib · Jul 23, 2013

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.

Related questions

Index for finding an element in a JSON array

I have a table that looks like this: CREATE TABLE tracks (id SERIAL, artists JSON); INSERT INTO tracks (id, artists) VALUES (1, '[{"name": "blink-182"}]'); INSERT INTO tracks (id, artists) VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]'); …

Read More
How do I modify fields inside the new PostgreSQL JSON datatype?

With postgresql 9.3 I can SELECT specific fields of a JSON data type, but how do you modify them using UPDATE? I can't find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious: postgres=# …

Read More
How do I query using fields inside the new PostgreSQL JSON datatype?

I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2. Specifically, given a series of JSON records: [ {name: "Toby", occupation: "Software Engineer"}, {name: "Zaphod", occupation: "Galactic President"} ] How would I write the SQL to …

Read More