Django annotate specific keys in json fields

Question

I'm using Django 1.9 and Postgres 9.5. I have a model (MyModel) with a JSONField (django.contrib.postgres.fields). The JSON value has the same structure in all the objects in the model.

{
"key1": int_val_1,
"key2": int_val_2
}

I want to order my queryset by a value of a specified key in my JSONField. Something like

MyModel.objects.annotate(val=F('jsonfield__key1')).order_by('val')

This does not work - i get the following error

Cannot resolve keyword 'key1' into field. Join on 'jsonfield' not permitted.

Is there anyway i can achieve this?

Possible duplicate of How to aggregate (min/max etc.) over Django JSONField data? — Mathijs
– Mathijs, Commented Apr 20, 2017 at 18:39

simon · Accepted Answer · 2018-03-19 04:24:56Z

28

for Django >= 1.11, you can use:

from django.contrib.postgres.fields.jsonb import KeyTextTransform
MyModel.objects.annotate(val=KeyTextTransform('key1', 'jsonfield')).order_by('val')

answered Mar 19, 2018 at 4:24

simon

16.5k5 gold badges47 silver badges68 bronze badges

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

4 Comments

Johannes Pertl Over a year ago

How would I use that for nested json fields? For example jsonfield['key1']['key2']

mlen108 Over a year ago

nested fields with KeyTextTransform is a bit PITA currently - e.g. MyModel.objects.annotate(val=KeyTextTransform('key1', 'jsonfield')).annotate(val1=KeyTextTransform('key2', 'val')).

mlen108 Over a year ago

so if you have loads of nested JSON fields then the Django query will be nested as hell too:

.annotate(     n=Cast(         KeyTextTransform(             'value',             KeyTextTransform(                 'field2',                 KeyTextTransform(                     'field1', 'data'),             ),         ),         output_field=FloatField())

. The logic for KeyTextTransform is to traverse the fields in reverse order.

Semih Sezer Over a year ago

This answer shows an excellent approach for nested fields: stackoverflow.com/a/55076146/5153500

alukach · Accepted Answer · 2021-03-11 20:53:06Z

4

An updated answer for this question:

At current time of writing (Django 3.1), you can now order exactly as you had hoped without needing any helper functions:

MyModel.objects.order_by('jsonfield__key1')

answered Mar 11, 2021 at 20:53

alukach

6,3483 gold badges42 silver badges42 bronze badges

1 Comment

rapto Over a year ago

But (v2.2) there seems to be a bug so this does not work in annotations.

Attia Haneef · Accepted Answer · 2022-02-18 11:01:05Z

0

JSONExtract is use with mysql Syntax ('json-field-name', 'json-field-key') ~Q is used for "not equal"

from django_mysql.models.functions import JSONExtract Listing.objects.annotate(weight=JSONExtract('package_dimensions','$.p>ackage_weight')).filter(~Q(weight=0))

edited Feb 18, 2022 at 11:01

answered Feb 18, 2022 at 10:55

Attia Haneef

12 bronze badges

Collectives™ on Stack Overflow

Django annotate specific keys in json fields

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related