CODEWITHSUNDEEP
mongodbsortingmongodb-query
Merc
Merc

Reputation: 17067

What does sort() do in MongoDB when sorting by a sub-field in an array?

Imagine having a Mongo collection like this:

[
  {  
     "roleName":"slave",
     "id":2,
     "__uc__roleName":"SLAVE",
     "_children":{  
        "managers":[  

        ]
     },
     "_id":ObjectId("5508e3e5875cbdcc712feda6"),
     "_clean":true
  },

  {  
     "__uc__roleName":"BOSS",
     "_children":{  
        "managers":[  
           {  
              "name":"Tony",
              "surname":"Mobily",
              "roleId":0,
              "id":0,
              "__uc__name":"TONY",
              "__uc__surname":"MOBILY",
              "_children":{  

              }
           }
        ]
     },
     "_clean":true,
     "_id":ObjectId("5508e3a9875cbdcc712feda4"),
     "id":0,
     "roleName":"boss"
  },

  {  
     "__uc__roleName":"EMPLOYEE",
     "_children":{  
        "managers":[  
           {  
              "name":"Chiara",
              "surname":"Mobily",
              "roleId":1,
              "id":1,
              "__uc__name":"CHIARA",
              "__uc__surname":"MOBILY",
              "_children":{  

              }
           },
           {  
              "name":"Sara",
              "surname":"Fabbietti",
              "roleId":1,
              "id":2,
              "__uc__name":"SARA",
              "__uc__surname":"FABBIETTI",
              "_children":{  

              }
           },
           {  
              "name":"Daniela",
              "surname":"Mobily",
              "roleId":1,
              "id":3,
              "__uc__name":"DANIELA",
              "__uc__surname":"MOBILY",
              "_children":{  

              }
           }
        ]
     },
     "_clean":true,
     "_id":ObjectId("5508e3e0875cbdcc712feda5"),
     "id":1,
     "roleName":"employee"
  }
]

What is the expected result of this query:

db.roles.find().sort( {'_children.managers.name': 1 } )

The actual result is:

1) Slave

2) Employee

3) Boss

But... why? How does sorting actually work, in case where you sort by a key within an array with multiple elements?

Upvotes: 3

Views: 2796

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

The ascending sort is working in exactly the way is should by considering the values present for the field reference you made within each item in the array and considering its value in comparison to other documents.

The ordering is probably best visualized when you consider the matched values from each document, in order they would be:

{ "roleName": "SLAVE",    "_children.managers.name": null },
{ "roleName": "BOSS",     "_children.managers.name":"Tony" },
{ "roleName": "EMPLOYEE", "_children.managers.name":"Chiara" }

That is because that would be the "least" ( ascending ) ordered value for that field in each document.

When sorting on the "_children.managers.name" in ascending order the document results are:

{ "roleName": "SLAVE",    "_children.managers.name": null },
{ "roleName": "EMPLOYEE", "_children.managers.name":"Chiara" }
{ "roleName": "BOSS",     "_children.managers.name":"Tony" },

Which is how you are seeing it.

So it's the "ordered" multi-key entry for the document value, and each value of the document considered against other.

Upvotes: 2

Related Questions