MongoDB sort doesn't yield consistent results with array fields

Question

this query:

db.getCollection('test_sort').find({}).sort({'sort_field': 1})

yields this result:

/* 1 */
{
    "_id" : ObjectId("55ca4705824fb4055859fc3d"),
    "sort_field" : [ 
        0, 
        ".", 
        55
    ]
}

/* 2 */
{
    "_id" : ObjectId("55ca4716824fb4055859fc3e"),
    "sort_field" : [ 
        0, 
        ".", 
        1
    ]
}

/* 3 */
{
    "_id" : ObjectId("55ca471e824fb4055859fc3f"),
    "sort_field" : [ 
        0, 
        ".", 
        22
    ]
}

/* 4 */
{
    "_id" : ObjectId("55ca4726824fb4055859fc40"),
    "sort_field" : [ 
        0, 
        ".", 
        84
    ]
}

/* 5 */
{
    "_id" : ObjectId("55ca472d824fb4055859fc41"),
    "sort_field" : [ 
        0, 
        ".", 
        12
    ]
}

This doesn't seem right. Does MongoDB ignore the contents of the array? It doesn't seem to, in other tests it seems to work as expected.

Based on gbot's answer, in the case of ascending, the smallest member of the array is used. Are there any workarounds for this? It's ok if the workaround requires a specific length of array, e.g. {"sort_field.0": 1, "sort_field.1": 1, "sort_field.2": 1} (my example doesn't seem to work either btw)

gbot · Accepted Answer · 2015-08-11 19:24:31Z

1

http://docs.mongodb.org/manual/reference/operator/aggregation/sort/

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.

answered Aug 11, 2015 at 19:24

gbot

411 bronze badge

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

2 Comments

quinn Over a year ago

ok. So all of these would be evaluated to zero, is that how I should interpret that? so they would all be the same(zero) for the sorting algorithm?

gbot Over a year ago

yes, if you were sorting desc then I would expect the order to be: 84, 55, 22, 12, 1 the details on the cross type comparison logic are included on the documentation page as well.

Collectives™ on Stack Overflow

MongoDB sort doesn't yield consistent results with array fields

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related