Django order_by on a nested json field

Question

My models are something like this.

class Foo():
    bar = models.ForeignKey('Bar', related_name='bar')
    payload = JSONField()

class Bar():
    candy = models.ForeignKey('Candy', related_name='candy')

class Candy():
    payload = JSONField()

My queryset looks something like this

# I want to order by a name on the json field
queryset = [
{
  "id": 1,
  "payload": {"age": 10, "company": "ccc"}
  "bar": 
   {
     'id': 1,
      "candy":
       {
        "payload": 
           {
           "names": ["text":"abc", "tag":"foo"], ["text":"abb", "tag":"bar"]
           }
       }
   }
 }, 
 {
  "id": 2,
  "payload": {"age": 12, "company": "aa"}
  "bar": 
   {
     'id': 2,
      "candy":
       {
        "payload": 
           {
           "names": ["text":"aaa", "tag":"bar"], ["text":"bbb", "tag":"bart"]
           }
       }
   }
 }]


foo = Foo.objects.all() #now I want to order foo by "names.text"

This is what I have tried so far

foo = foo.order_by(RawSQL("payload->>%s", ("age",))) #this works!!
foo = foo.order_by(RawSQL("bar.candy.payload->>%s", ("names[0].text",))) #does not work

The second statement does not work. I got inspiration to use RawSQL from here Django 1.9 JSONField order_by

I cannot figure out how to navigate to that particular class and then execute the query. What is the best way to do this?

score 0 · Answer 1 · answered Oct 30 '17 at 03:43

if you use RawSQL entered content must be sql query. the entered query only select Foo models objects.

first you must select related models content.

foo = Foo.objects.select_related("bar__candy")

in python shell if you enter

print foo.query

you can see database query entered and all fields names

after that you must enter RawSQL with selected entered field name.

if you see in query all fields names are in format

"<table name>"."<field name>"

if your app name is Main your candy table name in database is main_candy so you can enter RawSQL:

foo = foo.order_by(RawSQL("main_candy.payload->>%s", ("names[0].text",)))

and it must work.

Django order_by on a nested json field

1 Answers1