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.

have you looked over the docs and tried to make this query? Please update your question so we can help debug — about14sheep
– about14sheep, Commented Jan 6, 2022 at 19:06

Takis · Accepted Answer · 2022-01-06 20:10:22Z

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"]}])

edited Jan 6, 2022 at 20:10

answered Jan 6, 2022 at 19:56

Takis

8,7552 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB query using Mongoose

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related