Menu
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.
390
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
PostgreSQL offers two types for storing JSON data: json and jsonb . To implement efficient query mechanisms for these data types, PostgreSQL also provides the jsonpath data type described in Section 8.14. 7. The json and jsonb data types accept almost identical sets of values as input.
Json processes input faster than jsonb as there is no conversion involved in this. Jsonb converts the JSON data into the binary form so it has slightly slower input due to the binary conversion overhead. There is no change in the Schema design while working with JSON.
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
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
