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

It's tagged, but just to be clear: you're using mongoose, right? — markthethomas, Dec 17 '15 at 02:54
also, are you wanting to return one group or multiple groups? Another clarification: are each of these cities their own collection, individual objects, or what? — markthethomas, Dec 17 '15 at 02:57
All the same collection. Collection XYZ and all of these records are fields of it. — Fernanda Fentili, Dec 17 '15 at 02:59
cool; might be worth formatting a little differently. I'll add an edit. :) — markthethomas, Dec 17 '15 at 03:00
can you post the schema or at least the relevant parts of it? — markthethomas, Dec 17 '15 at 03:00
I'm waiting to return a group of records. Each group are represented for a city. For each city the last N records. This collection XYZ contains a timestamp field. — Fernanda Fentili, Dec 17 '15 at 03:01
so are you running an aggregation? More info about what you're currently doing and have tried === better answers :) — markthethomas, Dec 17 '15 at 03:02
Yes. It seems I might run a aggregation to perform this query. I'm new in mongo and also searching for something to solve it. Actually my question should be: "Is it possible to perform this query"? — Fernanda Fentili, Dec 17 '15 at 03:07
I'm looking for how to perform a query where I should have as result: 3 most recent titles of each city — Fernanda Fentili, Dec 17 '15 at 03:10
I'm getting error because I'm not so familiar with query aggregation in mongodb at all. btw I'm looking for fixing the query and post here a little bit more information — Fernanda Fentili, Dec 17 '15 at 03:16

Sarath Nair · Accepted Answer · 2015-12-17T05:57:07.807

7

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.

edited Dec 17 '15 at 05:57

answered Dec 17 '15 at 03:43

Sarath Nair

2,828
2
21
36

I got this error "unrecognized pipeline stage name $project" – Fernanda Fentili Dec 17 '15 at 04:06
{ [MongoError: exception: invalid operator '$slice'] ..} – Fernanda Fentili Dec 17 '15 at 04:16
How you mentioned, there's no slice for mongodb 3.0.x version that's mine. maybe I can find a other way over slice. – Fernanda Fentili Dec 17 '15 at 04:30
Is there an alternative for $slice? – Fernanda Fentili Dec 17 '15 at 04:38
1

No its not a map-reduce. Its a helper to save the result of aggregation to a new collection. It also helps in cases you don't have enough memory in system to complete aggregation. see https://docs.mongodb.org/v3.0/reference/operator/aggregation/out/ – Sarath Nair Dec 17 '15 at 05:56
I checked the database and could see a new collection called "aggr_out" as result of $out aggregation. Thanks buddy! – Fernanda Fentili Dec 17 '15 at 11:32
I performed this query oggr.update({}, {$push: {title: {$each:[], $slice:3}}}, function(err,raw) { console.log(err);console.log(raw); }) Do you see anything wrong? it is moongose query! – Fernanda Fentili Dec 17 '15 at 14:03
this is what I get from result { ok: 0, n: 0, nModified: 0 } – Fernanda Fentili Dec 17 '15 at 17:41
Has a bug to update multiple arrays in a single operation. I modified a little your solution col.find({},'_id',function(err,docs){ docs.forEach(function(doc) { col.update({ _id: doc['_id'] },{$push: {'title': {$each:[], $slice:5}}} }) }) – Fernanda Fentili Dec 18 '15 at 16:37

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

1 Answers1

Linked