Mongo db sorting on multikey index fields

Question

I was going through mongo db indexes and found this when i create index on multi key field and try to sort the result the behavior is strange.
For example:

> db.testIndexes.find();  
{ "_id" : ObjectId("584e6ca8d23d3b48f9cb819d"), "type" : "depart", "item" :  "aaa", "ratings" : [ 5, 8, 9 ] }  
{ "_id" : ObjectId("584e6cb2d23d3b48f9cb819e"), "type" : "depart", "item" : "aaa", "ratings" : [ 2, 3, 4 ] }  
{ "_id" : ObjectId("584e6cbdd23d3b48f9cb819f"), "type" : "depart", "item" : "aaa", "ratings" : [ 10, 6, 1 ] }  

db.testIndexes.createIndex({ratings:1});

Now if i sue these queries :

db.testIndexes.find().sort({ratings:1}).pretty();

Result is like this

{
    "_id" : ObjectId("584e6cbdd23d3b48f9cb819f"),
    "type" : "depart",
    "item" : "aaa",
    "ratings" : [
            10,
            6,
            1
    ]
}  
{
    "_id" : ObjectId("584e6cb2d23d3b48f9cb819e"),
    "type" : "depart",
    "item" : "aaa",
    "ratings" : [
            2,
            3,
            4
    ]
}  
{
    "_id" : ObjectId("584e6ca8d23d3b48f9cb819d"),
    "type" : "depart",
    "item" : "aaa",
    "ratings" : [
            5,
            8,
            9
    ]
}

and for query

db.testIndexes.find().sort({ratings:-1}).pretty();

Results are:

{
    "_id" : ObjectId("584e6cbdd23d3b48f9cb819f"),
    "type" : "depart",
    "item" : "aaa",
    "ratings" : [
            10,
            6,
            1
    ]
}  
{
    "_id" : ObjectId("584e6ca8d23d3b48f9cb819d"),
    "type" : "depart",
    "item" : "aaa",
    "ratings" : [
            5,
            8,
            9
    ]
}  
{
    "_id" : ObjectId("584e6cb2d23d3b48f9cb819e"),
    "type" : "depart",
    "item" : "aaa",
    "ratings" : [
            2,
            3,
            4
    ]
}

As results does not seems to follow and order so can anyone help how mongo is sorting these results.

Thanks
Virendra

guymaor86 · Accepted Answer · 2016-12-12 11:01:31Z

2

Well it does seem like the results are not following any order but actually they are. In your first sort {ratings:1}, what's happening here is the results are ordered by the smallest element in ratings. Since these are your lists:

[ 10, 6, 1 ]   [ 2, 3, 4 ]   [ 5, 8, 9 ]

So the list [ 10, 6, 1 ] smallest element is 1, the list [ 2, 3, 4 ] smallest element is 2 and the list [ 5, 8, 9 ] smallest element is 5. So the results are ordered in that way.

When you sort by descending, the same order happens but by maximum element in ratings.

Hope this helps.

answered Dec 12, 2016 at 11:01

guymaor86

4667 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

viren Over a year ago

Thanks @guvmaor86 this helped

Collectives™ on Stack Overflow

Mongo db sorting on multikey index fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related