CODEWITHSUNDEEP
arraysmongodbsorting
monkeyUser
monkeyUser

Reputation: 4671

sort array and Array of Object MongoDb

I have a simple Collection for understand sort in MongoDB

my documents are:

{
    "_id" : ObjectId("54b94985d74d670613e4fd35"),
    "tag" : [ 
        "A", 
        "B", 
        "Z"
    ]
}

{
    "_id" : ObjectId("54b949c9d74d670613e4fd36"),
    "tag" : [ 
        "D", 
        "E", 
        "F"
    ]
}

{
    "_id" : ObjectId("54b949dfd74d670613e4fd37"),
    "tag" : [ 
        "G", 
        "H", 
        "I"
    ]
}

When I sort by Tag I Have these results

db.candy.find().sort({tag:1})


{
    "_id" : ObjectId("54b94985d74d670613e4fd35"),
    "tag" : [ 
        "A", 
        "B", 
        "Z"
    ]
}

{
    "_id" : ObjectId("54b949c9d74d670613e4fd36"),
    "tag" : [ 
        "D", 
        "E", 
        "F"
    ]
}

{
    "_id" : ObjectId("54b949dfd74d670613e4fd37"),
    "tag" : [ 
        "G", 
        "H", 
        "I"
    ]
}

Instead with tag:-1

db.candy.find().sort({tag:-1})

{
    "_id" : ObjectId("54b94985d74d670613e4fd35"),
    "tag" : [ 
        "A", 
        "B", 
        "Z"
    ]
}

{
    "_id" : ObjectId("54b949dfd74d670613e4fd37"),
    "tag" : [ 
        "G", 
        "H", 
        "I"
    ]
}

{
    "_id" : ObjectId("54b949c9d74d670613e4fd36"),
    "tag" : [ 
        "D", 
        "E", 
        "F"
    ]
}

The results are very similar, the first object It's the same and Change only the second and the third. Same Results with Array of object. My question is: How it works the sort? I know that the letter A is the first letter of Alphabetic ( ASCII CODE ) and the Z is the last. The mongo check each element ( or object ) of array ? And Why the order inside array is the same when I use tag:-1 and tag:1 ? I expect something like

tag:1

{
    "_id" : ObjectId("54b94985d74d670613e4fd35"),
    "tag" : [ 
        "A", 
        "B", 
        "Z"
    ]
}

And tag:-1

{
    "_id" : ObjectId("54b94985d74d670613e4fd35"),
    "tag" : [ 
        "Z", 
        "A", 
        "B"
    ]
}

Upvotes: 0

Views: 3684

Answers (2)

BatScream
BatScream

Reputation: 19700

Just to re-iterate and elaborate on @marcinn, answer.

When the below statement is issued, it asks mongodb to sort the documents that were found by the query passed to the find() statement, in this case, all the documents in the collection would be returned by the find() function, by the tag field.

A point to note here is, the field tag is of type array and not a simple field.

db.candy.find().sort({tag:1})

If it were a simple field, the documents would have been sorted by the value in the tag field.

Anyway, mongodb needs a value by which it can sort the documents. To get the value of the field, mongodb does the following:

  • Checks if tag is an array. If it is an array, it needs to choose an element from the array whose value it can assume to be the weight of particular document.
  • Now it checks if the sort specified is in ascending or descending order.
  • If it is in ascending order, it finds the smallest element in the tag array, else the largest.
  • With the element chosen from the tag array, for each document, the sort is applied.

One important point to note here is that, the sort operation only changes the order of the root documents retrieved, in other words, it acts like an order by clause in SQL terms.

It does not change the order of the tag array elements for each document.

As a rule of thumb, a find() query, with limit, sort operations chained to it, does not change the structure of the retrieved documents. To manipulate the structure of the documents you need to perform an aggregate operation.

What you expect in your question, is achieved by manipulating the fields in each document, which only an aggregation operation can do.

So if you aggregate it as,

db.candy.aggregate([
{$match:{"_id":ObjectId("54b94985d74d670613e4fd35")}},
{$unwind:"$tag"},
{$sort:{"tag":-1}},
{$group:{"_id":"$_id","tag":{$push:"$tag"}}}
])

then you could get your result as:

{
    "_id" : ObjectId("54b94985d74d670613e4fd35"),
    "tag" : [ 
        "Z", 
        "A", 
        "B"
    ]
}

Upvotes: 3

marcinn
marcinn

Reputation: 1786

sort operator when sorting docs by array field, does the following:

  1. When sorting descending, it takes the biggest element from each array and compares with other

  2. When sorting ascending , it takes the smallest element from each array and compares with other

These are used only to sort the documents, so thats why the order inside the document is the same

With arrays, a less-than comparison or an ascending sort compares the smallest element of arrays, and a greater-than comparison or a descending sort compares the largest element of the arrays. As such, when comparing a field whose value is a single-element array (e.g. [ 1 ]) with non-array fields (e.g. 2), the comparison is between 1 and 2. A comparison of an empty array (e.g. [ ]) treats the empty array as less than null or a missing field.

http://docs.mongodb.org/manual/reference/method/cursor.sort/

Upvotes: 4

Related Questions