1

I have a Django model in my python project with a meta class detailing it's indexes. I'm curious if there's a way to create the index using the nested path of the json object. In this case we know the structure of our json and I wanted to stick with a BTree or Hash index on the specific element.

If I were simply running this as raw sql, I'd expect to just do something like:

CREATE INDEX ON foster_data(root->'level_1'->'level_2'->>'name');

I was hoping I could do something like this in my model:

from django.db import models
from django.contrib.postgres import indexes

class ParentGuardians(Facilitators): # which extends models.Model
    parent_identifier = models.IntegerField(db_column='p_id', default=None, blank=True,
                                           null=True)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['table_id', name='UniqueConstraint for Parents')
        ]
        indexes = [
            models.Index(fields=['p_id', ]),
            indexes.BTreeIndex(fields=[models.JSONField('{"root": {"level_1": {"level_2": "name"}}}'), ]
                        ,  name="jsonb_p_id_idx"),
        ]

or even:

...
            indexes.BTreeIndex(fields=["root->'level_1'->'level_2'->>'name'", ]
...

But the named field fields only wants strings and only wants them to be the top level field defined in the model.

I'm aware of this questions: Indexing JSONField in Django PostgreSQL but it seems more of a hack and wanted the result generated from the codebase and makemigrations, not to manually edit it. Is this possible more recently?

Poken1151
  • 570
  • 5
  • 20

1 Answers1

1

Django 3.2 introduced native support for these indexes.

The question as asked presently doesn't seem to have the definition of the JSONField, but assuming it is something like

from django.db import models

class Facilitators(models.Model):
    foster_data = models.JSONField()

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 Facilitators(models.Model):
    foster_data = models.JSONField()
    class Meta:
        indexes = [
            models.Index(models.F("foster_data__root__level_1__level2__name"), name="foster_data__root__level_1__level2__name_idx"),
        ]

This will create a B-Tree index. If you are adding these to an existing model, be sure to makemigrations and migrate.

See this answer as well https://stackoverflow.com/a/74619523/

kcontr
  • 343
  • 2
  • 12