Django PostgreSQL db_index on JSONField to query by exact key

Question

I have a Place model:

class Place(models.Model):

    ... some not interesting fields

    distances = models.JSONField()

In JSONField (distances) stores something like:

{
    '1': 10
    '2': 20
    '3': 30
    '4': 40
    ...
    '1000': 10000
}

where key is 'id' of some Location model, and value - 'time in road' to this Location from this Place

If website user selects Location, I show Places in order from closest to further to selected Location:

places = Place.objects.all().order_by(
    RawSQL('distances->%s', (str(selected_location_id),))
)

And everything is cool, except performance.

But, PostgreSQL support indexing for JSONB fields (which use Django for JSONField):

GIN, btree and hash

What differences between them? Which is suitable for my example?
Does django support db_index on JSONField? If yes, how to specify which index to use?

Big thx for help!

Possible duplicate of How to determine what type of index to use in Postgres? — e4c5
– e4c5, Commented Jun 26, 2016 at 0:34
A great deal of your question is answered by the linked question. The rest of it sounds like a situation where you shouldn't use jsonb — e4c5
– e4c5, Commented Jun 26, 2016 at 0:35

radtek · Accepted Answer · 2020-09-22 16:47:11Z

1

To index a particular key in json, create a raw sql migration:

Run ./manage.py makemigrations --empty yourApp where yourApp is the app of the model you want to change indexes for.
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.

Not sure if there is a native wrapper for this kind of thing in django, I looked and could not find but this should do it. You might want to put a note that this index is created via this migration in your model meta as well. Cheers!

answered Sep 22, 2020 at 16:47

radtek

36.6k13 gold badges149 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django PostgreSQL db_index on JSONField to query by exact key

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related