Django 1.9 JSONField order_by

Question

I have the following django model containing a 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 the data strong> 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 a query by one of the data fields? This request does not work.

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

Answer 1

As Julien said, ordering JSONFieldis not yet supported by Django. But this is possible through RawSQLusing the PostgreSQL Function for jsonb , In the case of OP:

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

Answer 2

Starting with Django 1.11, you can use django.contrib.postgres.fields.jsonb.KeyTextTransformRawSQL

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

In Django 1.10, you have to subclass KeyTransformyourself:

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

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

: KeyTransform KeyTextTransform , KeyTransform JSON- , KeyTextTransform .

, data {"test": "stuff"}, KeyTextTransform 'stuff', KeyTransform '"stuff"' ( json.loads)

Answer 3

, ASC DESC JSON , RawSQL, OrderBy. , , LOWER:

from django.db.models.expressions import RawSQL, OrderBy

RatebookDataEntry.objects.all().order_by(OrderBy(RawSQL("LOWER(data->>%s)", ("manufacturer_name",)), descending=True))

, :

RatebookDataEntry.objects.all().order_by(OrderBy(RawSQL("cast(data->>%s as integer)", ("annual_mileage",)), descending=True))

Answer 4

, ​​ Django 2.1, 2018 .

See https://code.djangoproject.com/ticket/24747 and https://github.com/django/django/pull/8528 for details.

Answer 5

The documentation does not mention such a possibility. It seems you cannot use order_by based on the JSON field at the moment.