How To Sort mongodb query results based on subdocuments

Question

i am having 2 documents in score collections(student)databases like below in mongodb database.

{ 
    id: 2, 
    type: 'newname', 
    subs: [
        { time: 20, val: 'b' },
        { time: 12, val: 'a' },
        { time: 30, val: 'c' }
    ] }, { 
    id: 1, 
    type: 'strs', 
    subs: [
        { time: 50, val: 'be' },
        { time: 1, val: 'ab' },
        { time: 20, val: 'cs' }
    ] }

How to construct a query to get the below result

{ 
    id: 1, 
    type: 'strs', 
    subs: [
        { time: 1, val: 'ab' },
        { time: 20, val: 'cs' },
        { time: 50, val: 'be' }
    ]
},
{ 
    id: 2, 
    type: 'newname', 
    subs: [
        { time: 12, val: 'a' },
        { time: 20, val: 'b' },
        { time: 30, val: 'c' }
    ]
}

ie: a query for find the documents based on time and have to sort the results on 2 criteria

1. by id ASC
2. by sub document time ASC

Answer 1

You can use cursor.sort() to sort on multiple fields (basically a combo) at the same time but I don't think it works when sorting on both a document and a subdocument field at the same time. If you were to sort on two different fields of the top document or on two different fields of a subdocument then it would be fine i guess.

So you can get a similar output using the aggregation framework. All you have to do is basically break down the arrays of the subs field and then sort them.

You could do something like:

db.col.aggregate({$unwind:'subs'}, {$sort:{id:1,'subs.time':1}});

With the above code you should get an output similar to this:

 { 
    id: 1, 
    type: 'strs', 
    subs: 
        { time: 1, val: 'ab' }
},{ 
    id: 1, 
    type: 'strs', 
    subs: 
        { time: 20, val: 'cs' }
},{ 
    id: 1, 
    type: 'strs', 
    subs: 
        { time: 50, val: 'be' }
},{ 
    id: 2, 
    type: 'newname', 
    subs: 
        { time: 12, val: 'a' }
},{ 
    id: 2, 
    type: 'newname', 
    subs: 
        { time: 20, val: 'b' }
},{ 
    id: 2, 
    type: 'newname', 
    subs: 
        { time: 30, val: 'c' }
}