Create index on nested element JSONField for Postgres in Django

Question

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?

Create index on nested element JSONField for Postgres in Django

0 Answers0