Django order_by on a nested JSONField

Question

I have JSONField on my Product model and in this model, I have nested fields.

For example:

"data": {
    'products':{
            "name": "ProductA", 
            "color": "Brown", 
            "price": {
                "end_user": 54,
                "credit_cart": 60
            },
            "name": "ProductB", 
            "color": "Red", 
            "price": {
                "end_user": 100,
                "credit_cart":120
             },

     }
}

I want to order nested end_user field.

this is my attempt:

Products.objects.order_by(RawSQL("data->>%s", ("data__price__end_user",)))

I found the answer:

queryset = queryset.annotate(
                end_user=RawSQL("data->'products'->'price'->'->'end_user'", [])
            ).order_by('end_user')

What database backend are you using? In what context do you need to order this object? Please add more specifics and what your desired outcome would be. — Lord Elrond, Mar 07 '19 at 06:06
@Jane I'm using ```PostgreSQL```. I have products and products have more than one price. I want to order ```end_user``` field. I want to get ```desc``` order. I've already try ```Products.objects.order_by(RawSQL("data->>%s", ("data__price__end_user",)))``` — Kaan Karaca, Mar 07 '19 at 06:23
please don't post code in comments. What server-side language are you using? — Lord Elrond, Mar 07 '19 at 06:27
I went ahead and edited your question with the information you gave me. You should always update your question with new information. — Lord Elrond, Mar 07 '19 at 06:29
Topic title is Django order_by on a nested JSONField. What do you expect? Python obviously. — Kaan Karaca, Mar 07 '19 at 06:30
what is wrong with your attempt? Is the order wrong, do you get null results? etc. — Lord Elrond, Mar 07 '19 at 06:34

score 0 · Answer 1 · answered Mar 07 '19 at 07:12

It looks like it is possible since Django 1.11 using KeyTextTransform

Django => 1.11

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

Django < 1.11

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

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

Source: https://stackoverflow.com/a/50046631/3345051

New Implementation (2.1): https://code.djangoproject.com/ticket/24747

In this problem, the user has one JSONField but I have nested JSONField. For example, manufacturer has 2 or more ```manufacturer_name``` name. In my issue, I want to order by ```end_user``` DESC or ASC. — Kaan Karaca, Mar 07 '19 at 07:56

score 0 · Answer 2 · answered Mar 07 '19 at 08:05

0

Is this work?

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

Or how about this:

entries = RatebookDataEntry.objects.all()
ordered_entries = sorted(entries, key=lambda x: x['data']['products']['price']['end_user'])

answered Mar 07 '19 at 08:05

Waket Zheng

5,065
2
17
30

For first one answer is no. And for the second one, I did not try because I don't want to sort with Python. I want to sort in the database side. – Kaan Karaca Mar 07 '19 at 10:31

Django order_by on a nested JSONField

2 Answers2