How to index JSON data in PostgreSQL 9.2?

Question

Does anyone know how to create an index for JSON data in PostgreSQL 9.2?

Sample data:

[ {"key" : "k1", "value" : "v1"}, {"key" : "k2", "value" : "v2"} ] 

Tell me, do I want to index all the keys, how to do this?

Thanks.

Answer 1

You are much better off using hstore for indexed fields, at least for now.

 CREATE INDEX table_name_gin_data ON table_name USING GIN(data); 

You can also create GIST indexes if you are interested in full-text search. More details here: http://www.postgresql.org/docs/9.0/static/textsearch-indexes.html

Answer 2

There are currently no built-in functions for directly indexing JSON. But you can do this with a function-based index, where the function is written in JavaScript.

See this blog post for more details: http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html

There is another blog entry that talks about JSON and how it can be used with JavaScript: http://www.postgresonline.com/journal/archives/272-Using-PLV8-to-build-JSON-selectors.html

Answer 3

This question is a bit old, but I think the answer chosen is actually not perfect. To index json (property values ​​inside json text), we can use expression indices with PLV8 (suggested by @a_horse_with_no_name).

Craig Kershtein does an excellent job explaining / demonstrating:

http://www.craigkerstiens.com/2013/05/29/postgres-indexes-expression-or-functional-indexes/