How to index JSON data in PostgreSQL 9.2?

Question

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

Example data:

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

Say if I want to index on all the keys how to do that?

Thanks.

Is there a reason why you're storing plain JSON and not separating into table columns? And any reason you don't accept answers? — Ian
– Ian, Commented Sep 25, 2012 at 4:57
I am evaluating the JSON type support PostgreSQL 9.2 and hence the need. I don't see a setting that disables receiving answers? Is there something like that? — ankurvsoni
– ankurvsoni, Commented Sep 25, 2012 at 5:07
Maybe take a look at: people.planetpostgresql.org/andrew/index.php?/archives/… — Ian
– Ian, Commented Sep 25, 2012 at 5:12

Steven Garcia · Accepted Answer · 2012-10-22 00:56:38Z

3

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 fulltext search. More info here: http://www.postgresql.org/docs/9.0/static/textsearch-indexes.html

answered Oct 22, 2012 at 0:56

Steven Garcia

2,8342 gold badges26 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2012-09-26 06:02:34Z

2

Currently there are no built-in functions to index JSON directly. But you can do it with a function based index where the function is written in JavaScript.

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

There is another blog post from which 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

answered Sep 26, 2012 at 6:02

user330315

3 Comments

ankurvsoni Over a year ago

But in my case - I need to index everything in the json object. Is there a way to do that?

user330315 Over a year ago

@ankurvsoni: no, I don't think so.

davetapley Over a year ago

Note that since 9.3 there are JSON Functions and Operators.

Reinsbrain · Accepted Answer · 2015-11-25 13:50:03Z

0

This question is a little old but I think the selected answer is not really the ideal one. To index json (the property values inside json text), we can use expression indexes with PLV8 (suggested by @a_horse_with_no_name).

Craig Kerstein does a great job of explaining/demonstrating:

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

answered Nov 25, 2015 at 13:50

Reinsbrain

2,6412 gold badges27 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to index JSON data in PostgreSQL 9.2?

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related