Create index on json field in PostgreSQL 9.2

Question

I have a few tables with json columns and would like to build an index on these columns. However, I get a default operator class (http://www.postgresql.org/docs/9.2/static/sql-createopclass.html). Has someone already done that or give me an alternative?

To reproduce the problem, try:

>> create table foo (json json, id int);
CREATE TABLE
>> create index on foo (id);
CREATE INDEX
>> create index on foo (json);
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.

The same applies for gist or gil indexes.

Interestingly, I don't get the error when I try the following:

>> create type "nested" as (json json, extra text);
CREATE TYPE
>> create table bar (id int, json nested);
CREATE TABLE
>> create index on bar (json);
CREATE INDEX

Is that because no index is created for the components?

Okay, the main issue is the default operator. Any help or shared experience with that is appreciated. Thanks.

Answer 1

there are no internal index type for JSON or XML type. This fields can hold a values, bat it cannot be indexes - you need to auxiliary columns with hstore columns or similar.

Answer 2

There is a workaround for this if you feel like installing the PLV8 JavaScript language:

http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html

Answer 3

The solution that works best for my case is the following:

I simply treat json as text and create an index on the text.

>> create index on foo ((json::text));

A query would then have to be transformed so that it uses the index.

Explain reveals whether the index is used or not.

>> explain select * from foo where json::text = 'foo';