CODEWITHSUNDEEP
node.jsmongodbmongoose
Abdul Aziz
Abdul Aziz

Reputation: 180

MongoDB query using Mongoose

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.

Upvotes: 1

Views: 108

Answers (1)

Takis
Takis

Reputation: 8693

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

Upvotes: 1

Related Questions