django orm group by json key in json field

Question

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

Answer 1

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