How to index just selected json key using Django functionality instead of all keys?

Question

I have the next model:

from django.contrib.postgres.fields import JSONField
from django.contrib.postgres.fields.jsonb import KeyTextTransform
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVectorField, SearchVector
from django.db import models


class ProfileUser(models.Model):
    name = JSONField()
    search_vector = SearchVectorField(null=True)

    class Meta:
        indexes = [GinIndex(fields=["search_vector"], name="user_full_name_gin_idx")]

    def save(self, *args, **kwargs):
        super(ProfileUser, self).save(*args, **kwargs)
        ProfileUser.objects.filter(pk=self.pk).update(search_vector=SearchVector('name'))

        # I have tried this, but this does not work.
        # ProfileUser.objects.filter(pk=self.pk).update(search_vector=SearchVector(KeyTextTransform('name', 'name')))

And name looks like [{'name': 'Random Name', 'lang': 'en'}, {'name': 'Какое-то Имя', 'lang': 'ru'}].

Currently the code above indexes both name and lang keys. Is it possible using Django to index just name key? I found how to workaround this using SQL directly, but I just want to know, if it is possible to do that just via Django.

I use Django 2.2 and PostgreSQL 12.

TitanFighter · Accepted Answer · 2021-01-30 16:25:03Z

3

Functional indexes is going to be available in Django 3.2 in April. This finally should allow to build needed indexes from Django.

answered Jan 30, 2021 at 16:25

TitanFighter

5,2044 gold badges55 silver badges81 bronze badges

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

Comments

Peter Bašista · Accepted Answer · 2020-12-08 05:41:50Z

2

In PostgreSQL, the feature which allows creating indices on parts of a JSON-structured field is called expression index. It needs to be used together with GIN index to achieve the desired effect. Such a usage is briefly mentioned in the jsonb documentation:

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

To the best of my knowledge, currently (December 2020) the only way to create such an index in Django is to issue the SQL statements manually.

However, there is an open ticket which asks for adding the support for expression indices to Django. The feature has been accepted but there is no patch yet and the ticket has not been updated for more than a year.

answered Dec 8, 2020 at 5:41

Peter Bašista

96711 silver badges25 bronze badges

1 Comment

Tim Tisdall Over a year ago

Support was added in 3.2 in this PR: github.com/django/django/pull/11929 However, I'm still confused on the proper syntax.

Simon Crane · Accepted Answer · 2019-12-26 00:39:17Z

1

Yes, it is possible, you can read the documentation for django indexing here: https://docs.djangoproject.com/en/3.0/ref/models/indexes/

Change this line:

indexes = [GinIndex(fields=["search_vector"], name="user_full_name_gin_idx")]

to this:

indexes = [GinIndex(fields=["name"], name="user_full_name_gin_idx")]

answered Dec 26, 2019 at 0:39

Simon Crane

2,1822 gold badges13 silver badges22 bronze badges

1 Comment

TitanFighter Over a year ago

This would not help, because fields=["name"] includes both fields (name and lang). fields means field names of model (not a key of JSON).

Collectives™ on Stack Overflow

How to index just selected json key using Django functionality instead of all keys?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related