CODEWITHSUNDEEP
pythonjsondjangopostgresql
Zhassulan Nurushev
Zhassulan Nurushev

Reputation: 133

django orm group by json key in json field

I'm using json field on my django model:

class JsonTable(models.Model):
    data = JSONField()
    type = models.IntegerField()

I tried next query, which works for normal sql fields:

JsonTable.objects.filter(type=1).values('type').annotate(Avg('data__superkey'))

But this throws next error:

FieldError: Cannot resolve keyword 'superkey' into field. Join on 'data' not permitted.

Is there way to make group by on json key, using Django ORM or some python lib, without use of raw sql?

Versions: Django 1.9b, PostgreSQL 9.4

UPDATE

Example 2:

JsonTable.objects.filter(type=1).values('data__happykey').annotate(Avg('data_superkey'))

throws same error on happykey

Upvotes: 9

Views: 2912

Answers (2)

Alexey Shrub
Alexey Shrub

Reputation: 1314

After some researching I found next solution:

from django.db.models import Count
from django.contrib.postgres.fields.jsonb import KeyTextTransform

superkey = KeyTextTransform('superkey', 'data')
table_items = JsonTable.objects.annotate(superkey = superkey).values('superkey').annotate(Count('id')).order_by()

I did not sure about order_by(), but documentation says that is needed. For another aggregation function type casting needed:

from django.db.models import IntegerField
from django.db.models.functions import Cast
superkey = Cast(KeyTextTransform('superkey', 'data'), IntegerField())

I test with another model, hope that write this code without misprints. PostgreSQL 9.6, Django 2.07

Upvotes: 2

franckfournier
franckfournier

Reputation: 364

If you are using this package https://github.com/bradjasper/django-jsonfield, there is nothing in the code for managing such simulated related queries (data__some_json_key) As Json data is text, you will have to go to raw sql or better : use queryset extra() method, but parsing Json in sql seems to be difficult.

Upvotes: 0

Related Questions