How to add Django GIN trgm index on a text value of a jsonb field, like field->>'value'?
- User
How to add Django GIN trgm index on a text value of a jsonb field, like field->>'value'?
Assistant
To add a GIN trigram (trgm) index on a text value extracted from a JSONB field in Django — for example,
field->>'value'— you need to use a functional index with the PostgreSQLgin_trgm_opsoperator class.This is useful for speeding up ILIKE / LIKE queries (especially with wildcards
%text%) on text values inside JSONB fields.
✅ Step-by-Step Guide
1. Prerequisites
- PostgreSQL with
pg_trgmextension enabled. - Django 3.2+ (supports functional indexes via
OpClassand*expressionsinIndex). - Your model has a
JSONField.
Enable
pg_trgmin your database (if not already):CREATE EXTENSION IF NOT EXISTS pg_trgm;You can run this manually or via a Django migration:
# In a migration file from django.db import migrations class Migration(migrations.Migration): dependencies = [ # your dependencies ] operations = [ migrations.RunSQL( "CREATE EXTENSION IF NOT EXISTS pg_trgm;", reverse_sql="DROP EXTENSION IF EXISTS pg_trgm;" ), ]
2. Define the Index in Your Model
Use
GinIndexwithOpClassandFexpressions to extract the JSONB key as text.# models.py from django.contrib.postgres.indexes import GinIndex from django.contrib.postgres.fields import JSONField # or use models.JSONField in newer Django from django.db import models from django.contrib.postgres.search import TrigramExtension from django.db.models import F, Value from django.contrib.postgres.indexes import OpClass class MyModel(models.Model): data = models.JSONField() # your JSONB field class Meta: indexes = [ # Create GIN trgm index on data->>'value' GinIndex( name='my_model_data_value_gin_trgm', fields=[], # leave empty because we use expressions opclasses=['gin_trgm_ops'], # specify operator class # Extract text value from JSONB expressions=[ OpClass( F('data__value'), # Django's double-underscore lookup for ->> name='gin_trgm_ops' ) ], ), ]💡 Note:
F('data__value')is equivalent todata->>'value'in SQL.
3. Alternative: Using Raw SQL Expression (if needed)
If you need more control or
F('data__value')doesn’t work for some reason (e.g., nested keys), you can useFunc:from django.db.models import Func, F from django.contrib.postgres.indexes import OpClass, GinIndex class JsonbExtractPathText(Func): function = 'jsonb_extract_path_text' output_field = models.TextField() # Then in Meta.indexes: GinIndex( name='my_model_custom_key_gin_trgm', fields=[], expressions=[ OpClass( JsonbExtractPathText(F('data'), Value('nested'), Value('key')), name='gin_trgm_ops' ) ], )This would index
data->'nested'->>'key'.
4. Create and Run Migration
python manage.py makemigrations python manage.py migrate
5. Querying Using the Index
Now you can efficiently query using
__icontains,__contains, etc.:MyModel.objects.filter(data__value__icontains='searchterm')PostgreSQL will use the GIN trgm index for such queries if the query planner deems it beneficial.
⚠️ Important Notes
- The index only works for text extraction (
->>), not raw JSONB (->). - Only usable for pattern matching (
LIKE,ILIKE,~, etc.) — not equality or range queries. - Make sure
pg_trgmis installed in your DB. - Functional indexes may not be used unless the query matches exactly (same expression). Test with
EXPLAIN ANALYZE.
✅ Example: Full Working Model
# models.py from django.db import models from django.contrib.postgres.indexes import GinIndex, OpClass class Product(models.Model): attributes = models.JSONField() class Meta: indexes = [ GinIndex( name='product_name_trgm_idx', fields=[], expressions=[ OpClass( F('attributes__name'), name='gin_trgm_ops' ) ] ) ]Then query:
Product.objects.filter(attributes__name__icontains='widget')
✅ Bonus: Verify Index Usage
In PostgreSQL shell:
EXPLAIN ANALYZE SELECT * FROM myapp_mymodel WHERE data->>'value' ILIKE '%foo%';Look for
Index Scan using ...to confirm the index is being used.
✅ Summary
✅ Use
GinIndexwithOpClass(..., name='gin_trgm_ops')
✅ Extract text usingF('field__key')→ translates tofield->>'key'
✅ Enablepg_trgmextension
✅ Useful foricontains,contains, etc.
✅ Always verify index usage withEXPLAIN ANALYZELet me know if you need support for nested keys or arrays!
- PostgreSQL with
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.