MongoDB group + sort doesn't work

Question

I have this situation. I have collection of posted messages. Messages can be shared (like on Facebook). Each time message is shared, a new one is created with an information about which message it's shared from, which message is the original one and the content may be altered. A single document looks like this:

{
    "_id": <id>
    "source": <source_id>   // if it's original, own id is used
    "shared_from": <message_id>
    "trending": 4,
    "created": <Datetime>
}

The trending field tells me the 'quality' of the message.

Now, here is the problem - I need to get some number of the best messages. I also need them to be unique by the source. Here's what I came up with and it should work (but it doesn't)

db.Messages.aggregate({
    { $sort: { trending: -1 } },
    { $group: { _id: "$source", doc_id: { $first: "$_id" } } },
    { $project: { _id: "$doc_id" } },
    { $limit: 6 }
})

When I do that, I get completely messed up result. It's not sorted at all. It's just random. Am I missing anything?

Answer 1

The result you get is not sorted because you did not sort it after the $group phase. You had sorted things by trending and took advantage of that when you were grouping to make sure that you only kept the _id value for each sort with the highest trending score. But there is no guarantee that $group will end up preserving any particular order so you should sort again, for which you will need to keep the trending score (assuming you intend to keep the order by trending score descending.

Something like this will work:

db.Messages.aggregate({
    { $sort: { trending: -1 } },
    { $group: { _id:"$source", doc_id:{$first: "$_id"}, trend:{$first:"$trending"}}},
    { $sort:{trend:-1} },
    { $project: { _id: "$doc_id" } },
    { $limit: 6 }
})