Mongo sort by multiple fields in a nested array

Question

I have a mongo collection with documents like-

{
_id : 1abc,
'data' :'blah',
'createdDate' : ISODate("2013-05-26T01:34:10Z"),
'a': [
    {
        _id : a1adas,
        'createdDate' : ISODate("2013-05-26T01:35:10Z"),
        'b':[
                {
                    _id : aadaasd
                    'createdDate' : ISODate("2013-05-26T01:37:10Z"),
                }
            ]
    }
]

}

I am required to sort the documents in reverse chronological order so that the document with the most recent createdDate , at any level, is first.

So far, I have this query, which I am not sure is working as expected.

db.collection.find({'data':'blah'}).sort({'createdDate':-1, 'a.createdDate': -1, 'a.b.createdDate': -1 }).explain();

I would like to know if there is a more efficient way of performing such a sort.

sfritter · Accepted Answer · 2013-09-19 15:33:31Z

Your query isn't doing what you'd like it to do. A MongoDB sort on multiple fields will handle each field in turn, not all three fields together. For example, I've inserted a number of test documents into a collection and I've run a similar sort:

> db.numbers.find().sort( { a : -1, b : -1, c : -1 })
{ doc : "doc1", "a" : 13, "b" : 5, "c" : 7 }
{ doc : "doc2", "a" : 12, "b" : 20, "c" : 91 }
{ doc: "doc3", "a" : 2, "b" : 50, "c" : 700 }

"doc3" has the lowest value of a, so it is returned last. However, it also has the highest value of any field in the sort, so in theory we'd like it to be the first thing returned. We can do this using MongoDB's aggregation framework:

db.numbers.aggregate(
{ $project : { 
    _id : 1, 
    a : 1, 
    maxNum : { $cond: [{ $gte : [ "$b", "$c" ]}, "$b" , "$c" ]} // max of b, c
    }},
{ $project : { 
   _id : 1, 
   maxNum : { $cond: [{ $gte : [ "$a", "$maxNum" ]}, "$a" , "$maxNum" ]} // max of a, b, c 
   }}, 
{ $sort : { maxNum : -1 } } 
)

This aggregation query will transform your data into a form where your sort can be properly performed. However, it isn't easy to see how this performs. There is currently no .explain() function for aggregation queries.

Collectives™ on Stack Overflow

Mongo sort by multiple fields in a nested array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related