[Django]-Indexing JSONField in Django PostgreSQL

by

14👍

✅

Django 3.2 introduced native support for these indexes, as Tim Tisdall commented in another answer. If you are adding these to an existing model, be sure to makemigrations and migrate.

To index a particular key, you combine an F expression with a JSONField path lookup on the model’s Meta indexes option:

from django.contrib.postgres.fields import JSONField   

class Document(models.Model):
    content = JSONField()
    class Meta:
        indexes = [
            models.Index(models.F("content__example_field_name"), name="content__example_field_name_idx"),
        ]

This will create a B-Tree index.

If you want to use filters, like has_key on a top-level field, (e.g. Document.objects.filter(content__has_key="example_field_name")), you may want a GinIndex as Mahesh H Viraktamath suggested in another comment.

from django.contrib.postgres.fields import JSONField   
from django.contrib.postgres.indexes import GinIndex

class Document(models.Model):
    content = JSONField()
    
    class Meta:
        indexes = [
            GinIndex("content", name="content_idx"),
        ]

Finally, for completeness, you can select your opclass by wrapping with the OpClass expression.

from django.contrib.postgres.fields import JSONField   
from django.contrib.postgres.indexes import GinIndex, OpClass

class Document(models.Model):
    content = JSONField()
    
    class Meta:
        indexes = [
            GinIndex(
                OpClass(models.F("content__example_field_name"), name="jsonb_path_ops"),
                name="content__example_field_name_idx",
            ),
        ]
👤kcontr

20👍

For those that want to index a particular key, create a raw sql migration:

  1. Run ./manage.py makemigrations --empty yourApp where yourApp is the app of the model you want to change indexes for.

  2. Edit the migration i.e.

operations = [
    migrations.RunSQL("CREATE INDEX idx_name ON your_table((json_field->'json_key'));")
]

Where idx_name is the name of the index, your_table is your table, json_field is your JSONField, and json_key in this case is the key you want to index.

Notice the -> operator in the index creation code line, and not ->> as mentioned here.

That should do it, but to verify all went well run the following sql:

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = '<your-table>';

and see if your index is there.

👤radtek

3👍

There is a bit more universal and Django native way. You can use following custom Migration Operation:

class CreateJsonbObjectKeyIndex(Operation):

    reversible = True

    def __init__(self, model_name, field, key, index_type='btree', concurrently=False, name=None):
        self.model_name = model_name
        self.field = field
        self.key = key
        self.index_type = index_type
        self.concurrently = concurrently
        self.name = name

    def state_forwards(self, app_label, state):
        pass

    def get_names(self, app_label, schema_editor, from_state, to_state):
        table_name = from_state.apps.get_model(app_label, self.model_name)._meta.db_table
        index_name = schema_editor.quote_name(
            self.name or schema_editor._create_index_name(table_name, [f'{self.field}__{self.key}'])
        )
        return table_name, index_name

    def database_forwards(self, app_label, schema_editor, from_state, to_state):
        table_name, index_name = self.get_names(app_label, schema_editor, from_state, to_state)
        schema_editor.execute(f"""
            CREATE INDEX {'CONCURRENTLY' if self.concurrently else ''} {index_name} 
            ON {table_name}
            USING {self.index_type}
            (({self.field}->'{self.key}'));
        """)

    def database_backwards(self, app_label, schema_editor, from_state, to_state):
        _, index_name = self.get_names(app_label, schema_editor, from_state, to_state)
        schema_editor.execute(f"DROP INDEX {index_name};")

    def describe(self):
        return f'Creates index for JSONB object field {self.field}->{self.key} of {self.model_name} model'

    @property
    def migration_name_fragment(self):
        return f'create_index_{self.model_name}_{self.field}_{self.key}'

Usage example:

from django.db import migrations

from util.migration import CreateJsonbObjectKeyIndex


class Migration(migrations.Migration):
    atomic = False  # Required if concurrently=True for 0 downtime background index creation

    dependencies = [
        ('app_label', '00XX_prev_migration'),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(
            database_operations=[
                # Operation to run custom SQL command. Check the output of `sqlmigrate` to see the auto-generated SQL
                CreateJsonbObjectKeyIndex(
                    model_name='User', field='meta', key='adid', index_type='HASH',
                    concurrently=True,
                )
            ],
        )
    ]

Tested with Django-2.2 and and AWS Postgres RDS, but should be compatible with other Django

👤dtatarkin

Leave a comment