How to create index on JSON field in Postgres?

Creating indexes for JSON fields in PostgreSQL first requires understanding the JSON data types and their indexing requirements. PostgreSQL provides two JSON data types: json and jsonb. The jsonb type is more efficient for storage and querying as it supports GiST and GIN indexes, whereas the json type does not support these indexes. It is generally recommended to use the jsonb type to leverage indexing benefits.

Step 1: Choose the appropriate JSON type

Since jsonb supports indexing, ensure that your table's JSON fields are of the jsonb type first. For example:

sql
CREATE TABLE example (
    id serial PRIMARY KEY,
    data jsonb
);

Step 2: Determine the index type

PostgreSQL supports multiple index types. For jsonb fields, it is common to use a GIN (Generalized Inverted Index), which is suitable for data structures containing key-value pairs and is highly effective for jsonb.

Step 3: Create a GIN index

Assume you want to create an index for a specific key within the jsonb field; you can do the following:

sql
CREATE INDEX idxgin ON example USING gin (data);

This creates a GIN index for the entire jsonb field, which is suitable for queries that need to retrieve the entire JSON document or a set of keys within the document.

Step 4: Index specific keys or paths

If your queries only access specific keys within the JSON document, you can create an index to index only those parts. For example, if you frequently query the user_id within the data field:

sql
CREATE INDEX idxgin_user_id ON example USING gin ((data -> 'user_id'));

Step 5: Use the index

After creating the index, when you execute queries involving these fields, PostgreSQL automatically uses these indexes. For example:

sql
SELECT * FROM example WHERE data ->> 'user_id' = '123';

This query leverages the idxgin_user_id index to improve query efficiency.

Example

Suppose we have an e-commerce platform database with an orders table that contains a jsonb field named details, storing order details such as product ID, quantity, and price. If we frequently need to query orders for specific products, we can create a GIN index for the product_id key within the details field:

sql
CREATE INDEX idxgin_product_id ON orders USING gin ((details -> 'product_id'));

This way, whenever we query orders for specific products, such as:

sql
SELECT * FROM orders WHERE details ->> 'product_id' = '1001';

PostgreSQL can leverage the idxgin_product_id index to quickly find orders with product ID '1001', significantly improving query performance.