How to get lastest N records of each group in mongodb?

Question

I have a couple of entries for each cities. How to get lastest 3 entries of each city? Like:

City1
record 1
record 2
record 3

City2
record 1
record 2
record 3

City3
record 1
record 2
record 3

The schema:

var schema = mongoose.Schema({
  city: {type: String},
  title: {type: String},
  created: {type: Number, default: Math.floor(new Date() / 1000)}
})

The code I've tried:

Collection
.find('location $in ' + cities)
.aggregate({$group: {location: "$location"}})
.sort('created: 1')
.limit(3).exec(function(err, docs) { res.render('index', { hash: docs }); });

So, how to perform a query where I should have as result: 3 most recent titles of each city

Answer 1

In mongoDB 3.2 you can perform this by using the aggregate query of following form:

db.collection.aggregate(
  {$sort: {created: -1}},
  {$group: {_id:'$city', title:{$push: '$title'}},
  {$project: {_id:0, city: '$_id', mostRecentTitle: {$slice: ['$title', 0, 2]}}}
)

Its very difficult to achieve the same using mongoDB 3.0. A very dirty trick is there to achieve this in 3.0. It involves a couple of steps and other collection.

First do an aggregate and output the result to a temporary collection called 'aggr_out'

Query:

db.collection.aggregate([
  {$sort: {created: -1}},
  {$group: {_id:'$city', title:{$push: '$title'}},
  {$project: {city: '$_id', mostRecentTitle: '$title'}},
  {$out: 'aggr_out'}]
)

Using above query, mostRecentTitle will have all the recent titles ordered from index 0, 1, 2, ... If you are happy with this result using this as you already have the result in indexes 0,1 and 2 of mostRecentTitle. Other titles can be simple ignored in application side.

Still if you are not happy, do an update on the output collection 'aggr_out', and read data from this collection. The query is,

db.aggr_out.update(
  {},
  {$push: {
    mostRecentTitle: {$each:[], $slice:3}
    }
  }
)

The above operation will slice the mostRecentTitle array so that it has most recent three titles. Do a read on this collection to get your desired result.