Question

0

436

Views

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.

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-05-12T20:27:24.754Z

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

0

436

MongoDB query using Mongoose

1 answers

0

Find remote jobs

Andres GPT