Indexing JSONField in Django PostgreSQL

Question

I am using a simple model with an attribute that stores all the data for that object in a JSONField. Think of it as way to transfer NoSQL data to my PostgreSQL database. Kinda like this:

from django.contrib.postgres.fields import JSONField   

class Document(models.Model):
    content = JSONField()

Each Document object has (more or less) the same keys in its content field, so I am querying and ordering those documents using those keys. For the querying and ordering, I am using Django's annotate() function. I recently came across this:

https://docs.djangoproject.com/en/2.1/ref/contrib/postgres/indexes/

I also know that PostgreSQL using JSONB, which is apparently indexible. So my question is this: Can I index my content field somehow to make my read operations faster for complex queries? And if so, then how do I do it? The documentation page I linked has no examples.

Answer 1

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.

Answer 2

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",
            ),
        ]

Answer 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