Django 1.9 JSONField order_by

Question

I have the following django model that contains JSONField:

class RatebookDataEntry(models.Model):
    data = JSONField(blank=True, default=[])
    last_update = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Ratebook data entries'

And data field contains this json:

{
    "annual_mileage": 15000, 
    "description": "LEON DIESEL SPORT COUPE", 
    "body_style": "Coupe", 
    "range_name": "LEON", 
    "co2_gkm_max": 122, 
    "manufacturer_name": "SEAT"
}

Can I sort queryset by one of the data fields? This query doesn't work.

RatebookDataEntry.objects.all().order_by("data__manufacturer_name")

Answer 1

As Julien mentioned ordering on JSONField is not yet supported in Django. But it's possible via RawSQL using PostgreSQL functions for jsonb. In OP's case:

from django.db.models.expressions import RawSQL
RatebookDataEntry.objects.all().order_by(RawSQL("data->>%s", ("manufacturer_name",)))

Answer 2

Since Django 1.11, django.contrib.postgres.fields.jsonb.KeyTextTransform can be used instead of RawSQL

from django.contrib.postgres.fields.jsonb import KeyTextTransform

qs = RatebookEntry.objects.all()
qs = qs.annotate(manufacturer_name=KeyTextTransform('manufacturer_name', 'data'))
qs = qs.order_by('manufacturer_name')
# or...
qs = qs.order_by('-manufacturer_name')

On Django 1.10, you'll have to subclass KeyTransform yourself:

from django.contrib.postgres.fields.jsonb import KeyTransform

class KeyTextTransform(KeyTransform):
    operator = '->>'
    nested_operator = '#>>'
    _output_field = TextField()

Note: the difference between KeyTransform and KeyTextTransform is that KeyTransform will return the JSON representation of the object, whereas KeyTextTransform will return the value of the object.

For example, if data is {"test": "stuff"}, KeyTextTransform will return 'stuff', whereas KeyTransform will return '"stuff"' (which can be parsed by json.loads)