15

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

Shaumux
  • 735
  • 11
  • 25

2 Answers2

19

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

Shaumux
  • 735
  • 11
  • 25
  • Is `KeyTransform` considered part of the non public api in Django? Seems like its not mentioned anywhere in the docs, as of Django 2. – Erick M Sep 18 '18 at 13:49
  • 1
    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 https://code.djangoproject.com/ticket/29482 – Shaumux Sep 18 '18 at 17:06
  • @Shaumux how can we evaluate `lev1` key if it is dynamic? meaning `lev` is key passed like `OuterRef` from a `Subquery` – Nwawel A Iroume Sep 25 '20 at 07:08
  • @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 – Shaumux Oct 06 '20 at 16:46
  • @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'))` – Nwawel A Iroume Oct 07 '20 at 17:21
  • @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 – Shaumux Oct 27 '20 at 13:16
4

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()
Alex Marandon
  • 4,034
  • 2
  • 17
  • 21
  • 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.` – dirkgroten Dec 01 '17 at 10:54
  • @dirkgroten 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' – Shaumux Dec 07 '17 at 12:21
  • 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`). – dirkgroten Dec 07 '17 at 14:47