Select top N rows from each group

user_775087 0
Viewed 15974

I use mongodb for my blog platform, where users can create their own blogs. All entries from all blogs are in an entries collection. The document of an entry looks like:

{
  'blog_id':xxx,
  'timestamp':xxx,
  'title':xxx,
  'content':xxx
}

As the question says, is there any way to select, say, last 3 entries for each blog?

aggregation-framework mongodb grouping limit
5 Answers

Starting in Mongo 5.2, it's a perfect use case for the new $topN aggregation accumulator:

// { blog_id: "a", title: "plop",  content: "smthg" }
// { blog_id: "b", title: "hum",   content: "meh"   }
// { blog_id: "a", title: "hello", content: "world" }
// { blog_id: "a", title: "what",  content: "ever"  }
db.collection.aggregate([
  { $group: {
    _id: "$blog_id",
    messages: { $topN: { n: 2, sortBy: { _id: -1 }, output: "$$ROOT" } }
  }}
])
// {
//   _id: "a",
//   messages: [
//     { blog_id: "a", title: "what",  content: "ever" },
//     { blog_id: "a", title: "hello", content: "world" }
//   ]
// }
// {
//   _id: "b",
//   messages: [
//     { blog_id: "b", title: "hum", content: "meh" }
//   ]
// }

This applies a $topN group accumulation that:

  • takes for each group the top 2 (n: 2) elements
  • top 2, as defined by sortBy: { _id: -1 }, which in this case means by reversed order of insertion
  • and for each record pushes the whole record in the group's list (output: "$$ROOT") since $$ROOT represents the whole document being processed.
user_92971440
Related