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

Epidaurus

So what is the issue you face ? – Algorism

django returns an error FieldError: Cannot resolve keyword 'lev1' into field. Join on 'context' not permitted. – Epidaurus

is this the exact json representation in your database ? – Algorism

What do you mean? The Postgres data type is Jsonb. The structure is the same is the database and what is shown here – Epidaurus

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

Epidaurus

Is KeyTransform considered part of the non public api in Django? Seems like its not mentioned anywhere in the docs, as of Django 2. – Punctuality

It can be viewed as a non public api, since its not documented anywhere, but at the same time, I find postgres specific things not to be as well documented as other parts of django, but its unlikely to change as it would effect too many things, have a look at this bug report making the same point code.djangoproject.com/ticket/29482 – Epidaurus

@Epidaurus how can we evaluate lev1 key if it is dynamic? meaning lev is key passed like OuterRef from a Subquery – Flank

@NwawelAIroume I'm not sure what you exactly mean by dynamic here, are the keys dynamic? You should still be able to use the Cast function to cast to JSON type and use the same methodology, there might be a lot of nested calls though – Epidaurus

@Shaumaux i noticed that the Cast help to extract the whole dict. but i want to extract only a value of the key of the dict. i wanted to do something like annotate(lev1=KeyTransform(OuterRef("external_model_column_value"), 'context')) – Flank

@NwawelAIroume annotate(lev1=KeyTransform(OuterRef("external_model_column_value"), 'context')) should work as long as the outerref evaluates to a string , its a bit difficult to undertstand without a proper schema or data though – Epidaurus

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()

Iscariot

How do you get the -> operator to work? I'd like to use it instead of a lot of KeyTransform, but get this error: django.db.utils.ProgrammingError: operator is not unique: unknown -> unknown LINE 1: SELECT ('step_data'->'step_data'->'workshop') AS "w" FROM "w... ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. – Arrearage

@Arrearage KeyTransform will use -> operator if you use a single level extraction like 'step_data' -> 'lev1', and it'll use #> operator if you use nested or chained annotation to extract nested data eg. 'step_data' -> 'lev 1' -> 'lev2' – Epidaurus

yep, I understand that, thanks for replying. But the problem I have is when using explicit select command like you show above. Making the query using the -> operator explicitly throws the error I showed (operator is not unique). – Arrearage