Search code examples
mongodbaggregation-frameworkgroupinglimit

Select top N rows from each group

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?

Solution

  • The only way to do this in basic mongo if you can live with two things :

    • An additional field in your entry document, let's call it "age"
    • A new blog entry taking an additional update

    If so, here's how you do it :

    1. Upon creating a new intro do your normal insert and then execute this update to increase the age of all posts (including the one you just inserted for this blog) :

      db.entries.update({blog_id: BLOG_ID}, {age:{$inc:1}}, false, true)

    2. When querying, use the following query which will return the most recent 3 entries for each blog :

      db.entries.find({age:{$lte:3}, timestamp:{$gte:STARTOFMONTH, $lt:ENDOFMONTH}}).sort({blog_id:1, age:1})

    Note that this solution is actually concurrency safe (no entries with duplicate ages).