16

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?

Mr T.
  • 4,278
  • 9
  • 44
  • 61
  • Possible duplicate of [How to aggregate (min/max etc.) over Django JSONField data?](http://stackoverflow.com/questions/34325096/how-to-aggregate-min-max-etc-over-django-jsonfield-data) – Mathijs Apr 20 '17 at 18:39

3 Answers3

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')
simon
  • 15,344
  • 5
  • 45
  • 67
  • 4
    How would I use that for nested json fields? For example jsonfield['key1']['key2'] – Johannes Pertl Jan 27 '21 at 14:24
  • nested fields with KeyTextTransform is a bit PITA currently - e.g. `MyModel.objects.annotate(val=KeyTextTransform('key1', 'jsonfield')).annotate(val1=KeyTextTransform('key2', 'val'))`. – mlen108 Aug 05 '21 at 11:08
  • 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. – mlen108 Aug 05 '21 at 11:15
  • This answer shows an excellent approach for nested fields: https://stackoverflow.com/a/55076146/5153500 – Semih Sezer Apr 04 '22 at 00:29
3

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')
alukach
  • 5,921
  • 3
  • 39
  • 40
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))

Attia Haneef
  • 1
  • 2