How to sort MongoDB aggregation with match, sort, and limit

Question

My current aggregation:

db.group_members.aggregate({ $match: { user_id: { $in: [1,2,3] } } }, { $group: { _id: "$group_id" } }, { $sort: { last_post_at: -1 } }, { $limit: 5 }) 

For document structure:

 { _id: '...', user_id: '...', group_id: '...', last_post_at: Date, } 

I also have an index on {user_id: 1, last_post_at: -1}

Since my index is already at last_post_at , is it useless? I am not 100% sure how this is ordered.

My ultimate goal is to replicate this SQL:

 SELECT DISTINCT ON (group_id) FROM group_members WHERE user_id in [1,2,3] ORDER_BY last_post_at DESC LIMIT 5 

I am wondering how to make it executive for very large group_members and still return it in the correct order.

UPDATE: I hope to find a solution that will limit the number of documents loaded into memory. This will be a rather large collection and access to it very often.

Answer 1

Place the $ sort before the $ group, otherwise MongoDB will not be able to use the index to sort.

However, in your request, it looks like you want to request a relatively small number of user_ids compared to the total size of your group_members collection. Therefore, I recommend the index only for user_id. In this case, MongoDB will have to sort your results in memory by last_post_at, but this is in exchange for using the index for the initial user_id search.