Django, Create GIN index for child element in JSON Array field

Question

I have a model that uses PostgreSQL and has field like this:

class MyModel(models.Model):
    json_field = models.JSONField(default=list)

This field contains data like this:

[
  {"name": "AAAAA", "product": "11111"},
  {"name": "BBBBB", "product": "22222"},
]

Now I want to index by json_field -> product field, because it is being used as identification. Then i want to create GinIndex like this:

class Meta:
    indexes = [
        GinIndex(name='product_json_idx', fields=['json_field->product'], opclasses=['jsonb_path_ops'])
    ]

When I try to create migration, I get error like this:

'indexes' refers to the nonexistent field 'json_field->product'.

How to create GinIndex that will be used for child attribute in Json Array?

Well, json_field -> 'product' won't return anything as the actual column value is an array. You would need json_field @> '[{product: "11111"}]' on the SQL level. And for that you need a GIN index on the column, not on an expression. — user330315
– user330315, Commented Apr 19, 2022 at 7:50
@a_horse_with_no_name So django has nothing to help me to create that kind of index inside my project? — Mr.D
– Mr.D, Commented Apr 19, 2022 at 7:55
Please don't use a JSONField: relational databases are not good with such data: work with a junction model with a ForeignKey to the Product. Only if the structure is not fixed or unknown, you should use a JSONField. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Apr 19, 2022 at 8:02
I don't know django, but you do not need an index on an expression. You only need a (GIN) index on the column itself. But Willem is right: using a properly normalized model is a much better approach to begin with. — user330315
– user330315, Commented Apr 19, 2022 at 8:17

willeM_ Van Onsem · Accepted Answer · 2022-04-19 08:30:34Z

5

Please don't use a JSONField ^[Django-doc] for well-structured data: if the structure is clear, like here where we have a list of objects where each object has a name and a product, it makes more sense to work with extra models, like:

class MyModel(models.Model):
    # …
    pass

class Product(models.Model):
    # …
    pass

class Entry(models.Model):
    my_model = models.ForeignKey(MyModel, on_delete=models.CASCADE)
    name = models.CharField(max_length=255)
    product = models.ForeignKey(Product, on_delete=models.CASCADE)

This will automatically add indexes on the ForeignKeys, but will also make querying simpeler and usually more efficient.

While databases like PostgreSQL indeed have put effort into making JSON columns easier to query, aggregate, etc. usually it is still beter to perform database normalization ^[wiki], especially since it has more means for referential integrity, and a lot of aggregates are simpeler on linear data.

If for example later a product is removed, it will require a lot of work to inspect the JSON blobs to remove that product. This is however a scenario that both Django and PostgreSQL databases cover with ON DELETE triggers and which will likely be more effective and safe when using the Django toolchain for this.

answered Apr 19, 2022 at 8:30

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

3 Comments

Sunderam Dubey Over a year ago

sir willem, one thing i have to ask from you is while registering models in admin.py, does it requires Admin suffix, for example i have a model car, and i register it with CarAdmin, so it requires admin to be suffix, as Form and View requires?

willeM_ Van Onsem Over a year ago

@SunderamDubey: it is not required (the same for Form or View), but it is the convention yes.

Sunderam Dubey Over a year ago

ok, sir willem, it also seems convention to me, that's why we have a admin class ModelAdmin with admin suffix.

Collectives™ on Stack Overflow

Django, Create GIN index for child element in JSON Array field

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related