Clash Royale CLAN TAG

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

happykey

2 Answers
2

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.

extra()

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

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.