Postgresql Index on JSON

Breath life into PostgreSQL JSON queries using a GIN index for broad utility:

-- GIN index for the win!
CREATE INDEX idx_gin_jsonb ON your_table USING gin (your_jsonb_column);


For queries on specific JSON keys, apply a functional GIN index for precision:

-- I've got the key, I've got the secret
CREATE INDEX idx_gin_key ON your_table USING gin ((your_jsonb_column -> 'key'));


In case of exact matches, resort to classic B-tree indexes on fields:

-- Start the tree house construction!
CREATE INDEX idx_btree_key ON your_table ((your_jsonb_column ->> 'key'));


To operate on numerical values within JSON keys, form a functional B-tree index with type casting:

-- Mathematics is an exact science
CREATE INDEX idx_btree_number_key ON your_table (((your_jsonb_column ->> 'key')::integer));


Remain nimble with lesser data variation by employing partial indexes:

-- When you know what you know
CREATE INDEX idx_partial_static_jsonb ON your_table USING gin (your_jsonb_column)
WHERE your_jsonb_column @> '{"known_key": "known_value"}';


ANALYZE to keep the database optimizer statistics in sync post indexing:

-- Feed the Analyze beast!
ANALYZE your_table;
