MongoDB query using Mongoose

Question

I have a photo collection which stores the user, tag, date and photo url. I want a query to return the 2 latest photo for a user per tag.

e.g

{user: 1, date: 1, url: a, tag: tag1}
{user: 1, date: 2, url: b, tag: tag1}
{user: 1, date: 3, url: c, tag: tag1}
{user: 1, date: 4, url: d, tag: tag2}
{user: 2, date: 1, url: e, tag: tag1}
{user: 3, date: 1, url: f, tag: tag1}

Running the query on user 1 should return

{user: 1, date: 1, url: a, tag: tag1}
{user: 1, date: 2, url: b, tag: tag1}
{user: 1, date: 4, url: d, tag: tag2}

I am using mongoose with NodeJs.

• about14sheep: have you looked over the docs and tried to make this query? Please update your question so we can help debug

Answer 1

Query

• in MongoDB 5 the easier way to do it is with $setWindowFields
• match the user
• group by tag(partition), sort by date, and rank(adds one field with the order of each document inside the its group)
• keep only rank < 3 (from each group keep only the 2 first)
• unset to remove the rank

*this gives the oldest, because in your expected output you have the oldest, you can change it to newest using {"date":-1}

*alternative solution for MongoDB <5 would be to sort by date, group by tag, and slice to get only the first 2.

Test code here

aggregate(
[{"$match":{"$user" : 1}},
 {"$setWindowFields":
 {"partitionBy":"$tag",
 "sortBy":{"date":1},
 "output":{"rank":{"$rank":{}}}}},
 {"$match":{"$expr":{"$lt":["$rank", 3]}}},
 {"$unset":["rank"]}])