How to get the last N elements with a matching key from a MongoDB collection

Question

I have a Collection of documents that looks like this:

[
 {
   "_id": "588cf61e5120ac11d4366f5c",
   "title": "This is a test entry",
   "entrybody": "This is the body of an entry",
   "creationdate": 1111,
   "author": "1223cllclclclc",
   "__v": 0
  },
 {
   "_id": "588cf6a556414f02f4e6a865",
   "title": "This is a new entry",
   "entrybody": "This is the body of an nuew entry",
   "creationdate": 11114040050505,
   "author": "1223cllclclclclslslsl",
   "__v": 0
 },
 {
   "_id": "588cf767fc2ede200cd5738e",
   "title": "This is a new entry",
   "entrybody": "This is the body of an nuew entry",
   "creationdate": 11114040050505,
   "author": "1223cllclclclclslslsl",
   "__v": 0
   }
  .....
]

I would like to get the last n records of each author and then order them by creation date, i have looked up how to do this with aggregation but im stuck.

I need the last N documents of the collection that match an especific key e.g author, and then i have to sort that result by "creation date". I think thats different from this question mongodb: how to get the last N records?

Thanks in advance.

That doesnt answer my question, it shows how to get the last N documents of a collection but nothing related to getting the last N records that match an expecific key and then sorts that result by creation date. — crdevdeu, Jan 28 '17 at 21:19
You just need to include query criteria. Try `db.collection.find({"author":"somevalue"}).sort({"creationda‌te":-1}).limit(10)`; — s7vr, Jan 28 '17 at 21:22
That would return the documents for just one author, i need the query to return last N documents for all authors. — crdevdeu, Jan 28 '17 at 21:23
Yes, but im stuck on how to implement aggregations to get what i need. — crdevdeu, Jan 28 '17 at 21:30

s7vr · Accepted Answer · 2017-01-29T00:34:21.370

1

You can try something like this.

$sort to process the records for each author on last creationdate.

$group and $project to pick last N records for each author.

$unwind and $sort to process records by creationdate.

db.collection.aggregate({
    $sort: {
        author: 1,
        creationdate: -1
    }
}, {
    $group: {
        _id: "$author",
        data: {
            $push: "$$ROOT"
        }
    }
}, {
    $project: {
        _id: 0,
        lastN: {
            $slice: ["$data", N]
        }
    }
}, {
    $unwind: "$lastN"
}, {
    $sort: {
        "lastN.creationdate": -1
    }
})

edited Jan 29 '17 at 00:34

answered Jan 28 '17 at 21:40

s7vr

73,656
11
106
127

Thank you, that almost did the trick for me, i only had to sort again the resulting array of objects by creationdate with .sort() to make it work as i needed. – crdevdeu Jan 29 '17 at 00:26
Np. You don't have to sort. Fixed the answer as it was missing reference. The last sort is what is need. – s7vr Jan 29 '17 at 00:34

score 0 · Answer 2 · answered Jan 28 '17 at 22:43

Here is an alternative way to solve the problem with help of forEach method:

var result = {};
db.collection.find().sort({"author": 1, "creationda‌‌te":-1}).forEach(function(doc) { 
    if (!(doc.author in result)) { 
        result[doc.author] = [];
    }
    if (result[doc.author].length < 5) { 
        result[doc.author].push(doc); 
    } 
})

After execution, the result variable is filled with documents separated by author's name like

{
    'author1': [
        {doc1},
        {doc2}
    ],
    'author2': [
        {doc1},
        {doc2}
    ]
}

How to get the last N elements with a matching key from a MongoDB collection

2 Answers2