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

Valaria

Not that I know of (on a queryset), but this kind of indicates to me that you might actually need an object model for "data" instead of json – Brinkman

As an aside, use default=list instead of default=[], otherwise you'll end up with the same list shared between different instances. – Luhe

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",)))

Purpure

And in case you want DESC ordering, you can introduce annotated field and order by it. Like this: RatebookDataEntry.objects.annotate(manufacturer_name=RawSQL("data->>%s", ("manufacturer_name",)).order_by("-manufacturer_name") – Hebron

Just in case anyone is only looking at the accepted answer, this is a feature in Django 2.1, see my answer for links. – Lilian

This doesn't work for MySQL though, any work around for MySQL? – Constrictor

Use -> instead of ->> if you want to order numeric values. (->> gets JSON object field as text) – Christoperchristoph

Answer 2

This question (and most of the answers) are for Django 1.9. However, Django versions 3.1 and newer support JSONField on recent versions of MariaDB, MySQL, Oracle, PostgreSQL, and SQLite.

The behavior around creating/maintaining indexes on JSON fields may vary across database engines, but ordering should work with the exact syntax you have in your question:

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

Note that unless you do further filtering, this will include database rows where the manufacturer_name key in the data JSONField does not exist.

Baudelaire

Answer 3

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)

Scabious

def what I needed – Dominoes

this should be the preferred answer – Osier

My json field has data in below format, how can I use it to sort based on first ip address in the dictionary? <QuerySet [{'ip_addresses': {'172.19.128.1': 1, '192.168.0.129': 1}}, {'ip_addresses': {'10.248.91.72': 1, '10.248.91.73': 1, '10.248.91.74': 1}}, {'ip_addresses': {'1.1.34.13': 1}}]> – Dispeople

Answer 4

Following Daniil Ryzhkov answer and Eugene Prikazchikov comment, you should be able to sort ASC and DESC on JSON data fields without annotating your queryset, by using both RawSQL and OrderBy. Also, you can perform case insensitive sorting by adding LOWER:

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

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

To compare integers fields, you can cast as integer:

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

Cheddite

Answer 5

This is an upcoming feature which has already been added and will be released in Django 2.1, expected release of August 2018.

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

Lilian

see for example docs.djangoproject.com/en/3.2/ref/models/querysets/#order-by – Kalie

Answer 6

The documentation does not mention this possibility. It seems you cannot use order_by based on a JSONfield for the moment.

Cadena

Answer 7

I had to do the following to order by date (using to_date). Assuming there's another value in data called created_date (e.g. 03.06.2019).

RatebookDataEntry.objects.all().order_by(
        OrderBy(
            RawSQL("to_date(values->>%s, 'DD.MM.YYYY')", ("created_date",)),
            descending=True,
        )
    )

Pastore