Postgres: values query on json key with django

Question

I need to do a values/values_list query on nested key on a postgres backed jsonfield in django 1.10 eg.

class AbcModel(models.model):
    context = fields.JSONField()

If it has values like:

{
  'lev1': {
    'lev': 2
  }
}

I want to run a queries like

AbcModel.objects.values('context__lev1__lev2').distinct()
AbcModel.objects.values_list('context__lev1__lev2', flat=True).distinct()

EDIT: The JSON fields are the official django JSONField from django.contrib.postgres.fields

Answer 1

So I found a solution, this works with django 1.10 and above. I used the KeyTransform to annotate and extract the nexted key and did a values_list on that.

from django.contrib.postgres.fields.jsonb import KeyTransform
extracted_query = AbcModel.objects.annotate(lev1=KeyTransform('lev1', 'context')).annotate(lev2=KeyTransform('lev', 'lev1'))

This query allows me to use lev1 and lev2 as normal fields in the model, so I can do a values, values_list or any other valid query on the fields.

Django 1.11 allows to nest the the two Transforms in one annotate, not sure about 1.10 about the nesting as I have upgraded to 1.11

Answer 2

It's not ideal, but I was able to get this working by adding the json field as an extra field and then calling values on that extra field:

AbcModel.objects.extra(select={
    "extra_field": "context->'lev1'->'lev2'"
}).values('extra_field').distinct()
AbcModel.objects.extra(select={
    "extra_field": "context->'lev1'->'lev2'"
}).values_list('extra_field', flat=True).distinct()