1---
2title: Index JSONB Columns for Efficient Querying
3impact: MEDIUM
4impactDescription: 10-100x faster JSONB queries with proper indexing
5tags: jsonb, gin, indexes, json
6---
7 
8## Index JSONB Columns for Efficient Querying
9 
10JSONB queries without indexes scan the entire table. Use GIN indexes for containment queries.
11 
12**Incorrect (no index on JSONB):**
13 
14```sql
15create table products (
16  id bigint primary key,
17  attributes jsonb
18);
19 
20-- Full table scan for every query
21select * from products where attributes @> '{"color": "red"}';
22select * from products where attributes->>'brand' = 'Nike';
23```
24 
25**Correct (GIN index for JSONB):**
26 
27```sql
28-- GIN index for containment operators (@>, ?, ?&, ?|)
29create index products_attrs_gin on products using gin (attributes);
30 
31-- Now containment queries use the index
32select * from products where attributes @> '{"color": "red"}';
33 
34-- For specific key lookups, use expression index
35create index products_brand_idx on products ((attributes->>'brand'));
36select * from products where attributes->>'brand' = 'Nike';
37```
38 
39Choose the right operator class:
40 
41```sql
42-- jsonb_ops (default): supports all operators, larger index
43create index idx1 on products using gin (attributes);
44 
45-- jsonb_path_ops: only @> operator, but 2-3x smaller index
46create index idx2 on products using gin (attributes jsonb_path_ops);
47```
48 
49Reference: [JSONB Indexes](https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING)
50