Multiple ways to create index on a json field's nested property in PostgreSQL 9.3

json postgresql indexing postgresql-9.3 postgresql-json

You must use GIN index for JSON and JSONB datatype.You can use operator parameters for your planned queryExamples:

CREATE INDEX idx_tbl_example ON tbl_example USING GIN(your_jsonb_field);

If you are planning only use @> operator, you can use with jsonb_path_ops parameter

CREATE INDEX idx_tbl_example ON tbl_example USING GIN(your_jsonb_field jsonb_path_ops);

I think you can use this:

CREATE INDEX idx_tbl_example ON tbl_example USING GIN(your_jsonb_field json_extract_path_text);

CodeHunter