Blog · Oracle

Why order_by() sorts 10 before 2 on Oracle

A Django query that orders by a number inside a JSONField returns the wrong order on Oracle. The value 10 comes first, then 100, then 2. The data is correct. The comparison is not.

# models.py
class Job(models.Model):
    data = models.JSONField(default=dict)

Job.objects.create(data={"qty": 2})
Job.objects.create(data={"qty": 10})
Job.objects.create(data={"qty": 9})
Job.objects.create(data={"qty": 100})

Job.objects.order_by("data__qty").values_list("data__qty", flat=True)
# Oracle:     [10, 100, 2, 9]
# PostgreSQL: [2, 9, 10, 100]

An order_by() on a key becomes ORDER BY <key transform> in the SQL. Django builds the transform, and the database performs the comparison. Oracle extracts the value as text and compares it as text. In text order the character 1 comes before the character 2, so 10 and 100 come before 2.

Cast the value before the sort

Wrap the key in Cast with a numeric output_field. The SQL then asks the database for a number, and Oracle compares numbers.

from django.db.models import IntegerField
from django.db.models.functions import Cast

(Job.objects
    .annotate(qty=Cast("data__qty", IntegerField()))
    .order_by("qty"))
# Oracle: [2, 9, 10, 100]

The same annotation is correct on all five databases, so one code path covers every backend. A FloatField or a DecimalField works in the same way for other numeric types.

Why an index does not help

A functional index on the JSON key speeds the sort up, and it does not change the result. The index stores the same extracted text and orders it in the same way. Build the index on the cast expression, not on the raw key, to keep the index and the query in agreement.