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

django returns an error FieldError: Cannot resolve keyword 'lev1' into field. Join on 'context' not permitted. — Shaumux
– Shaumux, Commented Mar 8, 2017 at 16:04
What do you mean? The Postgres data type is Jsonb. The structure is the same is the database and what is shown here — Shaumux
– Shaumux, Commented Mar 8, 2017 at 16:12

Shaumux · Accepted Answer · 2017-07-28 09:36:14Z

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

answered Jul 28, 2017 at 9:36

Shaumux

74512 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Erick M Over a year ago

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

Shaumux Over a year ago

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

Nwawel A Iroume Over a year ago

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

Shaumux Over a year ago

@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

Nwawel A Iroume Over a year ago

@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'))

|

Alex Marandon · Accepted Answer · 2017-07-25 14:19:18Z

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

answered Jul 25, 2017 at 14:19

Alex Marandon

4,1232 gold badges19 silver badges22 bronze badges

3 Comments

dirkgroten Over a year ago

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.

Shaumux Over a year ago

@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'

dirkgroten Over a year ago

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

Collectives™ on Stack Overflow

Postgres: values query on json key with django

2 Answers 2

6 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Linked

Related