CODEWITHSUNDEEP
mongodb
Piercy
Piercy

Reputation: 809

MongoDB - Most Recent 20 unique

I'm trying to get the most recent unique value from a collection in MongoDB, however, the $group seems to be breaking the $sort somehow. Ie, in this case, im trying to get the most recent activity for a user (but only returning 20 activities total).

db.activities.aggregate([{ $sort: { timestamp:-1, firstname: 1 } }, { $limit: 20 }])

If i run the above, i get 20 results back and in the correct order, however, i have multiple doduments with the same firstname as the user has submitted multiple activities.

If i run the below, the top user from the first query is no longer in the list. I get a completely different set of people back.

db.activities.aggregate([{ $sort: { timestamp:-1, firstname: 1 } },  { $group: { _id: "$firstname"} } , { $limit: 20 }])

How can i get the most recent activity per person, but limiting it to only 20 activities?

Upvotes: 0

Views: 129

Answers (1)

Lahori
Lahori

Reputation: 1091

You need to first find unique values, then apply limit and in the end just sort however you want. Its your solution with right ordering:

db.activities.aggregate([  
     { $group: { _id: "$firstname"} } , 
     { $limit: 20 },
     { $sort: { timestamp:-1, firstname: 1 } },
])

Edit: Above code will not return correct result because from docs $group doesn't respect ordering. Instead you should $sort them before selecting first 20 like this:

db.activities.aggregate([  
     { $group: { _id: "$firstname"} } ,
     { $sort: { timestamp:-1, firstname: 1 } },
     { $limit: 20 },
])

This will return unique docs but if you need only recent docs without unique constraint then just remove the $group stage as its redundant and will consume performance exponentially. If you still need unique docs with better performance then you need to make time-related decision i.e. $match the docs greater than last 1 hour, $group them, $sort and $limit first 20.

Upvotes: 1

Related Questions