How to get the last N records in mongodb?

Question

I can't find anywhere it has been documented this. By default, the find() operation will get the records from beginning. How can I get the last N records in mongodb?

Edit: also I want the returned result ordered from less recent to most recent, not the reverse.

Huoh

@Haim, plase be specific to answer, which part of the web page resolves my question? – Huoh

Hi @BinChen, I have the same problem recently, is it solved? – Streamy

Answer 1

If I understand your question, you need to sort in ascending order.

Assuming you have some id or date field called "x" you would do ...

.sort()

---

db.foo.find().sort({x:1});

The 1 will sort ascending (oldest to newest) and -1 will sort descending (newest to oldest.)

If you use the auto created _id field it has a date embedded in it ... so you can use that to order by ...

db.foo.find().sort({_id:1});

That will return back all your documents sorted from oldest to newest.

Natural Order

---

You can also use a Natural Order mentioned above ...

db.foo.find().sort({$natural:1});

Again, using 1 or -1 depending on the order you want.

Use .limit()

---

Lastly, it's good practice to add a limit when doing this sort of wide open query so you could do either ...

db.foo.find().sort({_id:1}).limit(50);

or

db.foo.find().sort({$natural:1}).limit(50);

Vasculum

This approach does not work when I use skip and limit with it:Item.find().sort('date',-1).skip(start).limit(limit).run(cb); It should sort the rows and then skip the first N rows. but it skips the first N rows and then sorts the remained. – Cysteine

@MortezaM. I'm pretty sure you've got your query order mixed up ... your sort() should be run last, not first (much like a SQL ORDER BY) .find({}).skip(1).limit(50).sort({"date":-1}) – Vasculum

What ever it is, the order of calling functions should have nothing to do with the end result. – Cysteine

@MortezaM. Of course the order matters. item = 3; item.add(3).divide(3) == 2; item.divide(3).add(3) == 4; With no order what would be the outcome??? I agree with you that this reversed order is not intuitive. This is no SQL after all it should follow normal OO paradigms. – Disconsider

and yet the order DOES NOT matter. All you have to do is try it to see that it does not. all of these are called on the cursor and passed to the server so the server limits the results of the sort (top N) as anything else wouldn't make sense. – Idioplasm

Docs confirm that the order doesn't matter: link – Robbins

Doesn't MongoDB save the document in the inserted order? (Object id ascending)? – Governorship

Once you do db.foo.find().sort({$natural:-1}).limit(50);, you get the last 50 entries in a descending way. How do we optimally change the order to ascending? – Quaky

maybe add manual .to_a.sort_by { |doc| doc.id } after that? Or use moped functions directly and reorder it. – Mikimikihisa

@Justin Jenkins - this is terrible in terms of performance when you have a table with LOOOOOOTS of records. – Prismatic

@Maimonides what does "LOOOOOOTS" mean? I've got collections with 100s of millions of documents, but perhaps that's not lots to you. As a rule of thumb once you are talking about querying documents at any sort of scale you should be creating indexes on the key you wish you query on. :) – Vasculum

@Justin Jenkins If it works well for 100s of millions it has to use an indexed field for: "Assuming you have some id or date field called "x" you would do...", but I didn't see you mention it. Did you use an indexed field there? – Prismatic

@JustinJenkins the order of skip/limit/sort does not matter: "the order in which you chain the limit() and the sort() methods is not significant" "Combine Cursor Methods". Any chained methods on the Cursor object modify the cursor before any records are received; MongoDB will apply the correct ordering of the skip/limit/sort parameters intuitively. – Isabellaisabelle

this should not be accepted as the answer as it doesn't actaully do what the OP asked specifically: 'get the last N records' and ''result ordered from less recent to most recent, not the reverse'. This solution will get read N records from the tail, but it will lose it's ascending order due to the initial ordering operation which means you will need to re-order the results back to natural : 1 order – Mickelson

Natural order should not be relied upon; if you are using a replica set (and you should be), different nodes might well have the same documents stored in a different order on disk. – Ingrowth

date part in auto created _id is one part of _id and it can not be used for 100% accuracy to sort by date. It works only if you never ever have more than 1 write/second docs.mongodb.com/manual/reference/method/ObjectId – Souse

Please include @João Otero 's answer which contemplates performance so this answer can be really complete – Koerner

"If you use the auto created _id field it has a date embedded in it", I came here looking for silver and instead found gold. Thank you. – Postrider

Answer 2

The last N added records, from less recent to most recent, can be seen with this query:

db.collection.find().skip(db.collection.count() - N)

If you want them in the reverse order:

db.collection.find().sort({ $natural: -1 }).limit(N)

If you install Mongo-Hacker you can also use:

db.collection.find().reverse().limit(N)

If you get tired of writing these commands all the time you can create custom functions in your ~/.mongorc.js. E.g.

function last(N) {
    return db.collection.find().skip(db.collection.count() - N);
}

then from a mongo shell just type last(N)

Kryska

db.collection.find().reverse().limit(1) gives me the error ... has no method reverse – Klondike

@Klondike you are right, I just noticed that reverse() was added by [Mongo-Hacker ](tylerbrock.github.com/mongo-hacker), I'll update my answer. Thanks. – Kryska

db.getCollection('COLLECTION_NAME').find().skip(db.getCollection('COLLECTION_NAME').count()-N) working great for me :) – Kalgoorlie

This should be the answer to the question, and not the answer by Justin Jenkins. – Delphinium

Natural order should not be relied upon; if you are using a replica set (and you should be), different nodes might well have the same documents stored in a different order on disk. – Ingrowth

Is it possible to do this in an aggregate search where you find the last N values of multiple documents in that are aggregated? – Wolbrom

Right on! +1 thank you so much this clears up the answer much better than the first question – Frans

@KelvinU did you find any way, to find solution for - "Is it possible to do this in an aggregate search where you find the last N values of multiple documents in that are aggregated?" – Envoi

Answer 3

In order to get last N records you can execute below query:

db.yourcollectionname.find({$query: {}, $orderby: {$natural : -1}}).limit(yournumber)

if you want only one last record:

db.yourcollectionname.findOne({$query: {}, $orderby: {$natural : -1}})

Note: In place of $natural you can use one of the columns from your collection.

Jayjaycee

this works for me db.yourcollectionname.findOne({$query:{}, $orderby : {$natural : -1}}). I think last parathensis is missing in the answer – Animadvert

Natural order should not be relied upon; if you are using a replica set (and you should be), different nodes might well have the same documents stored in a different order on disk. – Ingrowth

Answer 4

Sorting, skipping and so on can be pretty slow depending on the size of your collection.

A better performance would be achieved if you have your collection indexed by some criteria; and then you could use min() cursor:

First, index your collection with db.collectionName.setIndex( yourIndex ) You can use ascending or descending order, which is cool, because you want always the "N last items"... so if you index by descending order it is the same as getting the "first N items".

Then you find the first item of your collection and use its index field values as the min criteria in a search like:

db.collectionName.find().min(minCriteria).hint(yourIndex).limit(N)

Here's the reference for min() cursor: https://docs.mongodb.com/manual/reference/method/cursor.min/

Oilcloth

Only answer that takes performance into account, great addition :) – Koerner

Does this answer guarantees order? – Koerner

Apparently you can just forget the min and use: db.collectionName.find().hint(yourIndex).limit(N) If the index is in descending order you get the N minimum values. – Standice

the order is guaranteed by your index – Forsythia

What about finding first N records? does this method can be apply? – Tagmemics

yes, just use the index in ascending order – Forsythia

Answer 5

 db.collection.find().sort({$natural: -1 }).limit(5)

Terminal

How can I perform this query in springboot? – Predesignate

But the resulting order is most recent first, not most recent last. – Chancemedley

Answer 6

@bin-chen,

You can use an aggregation for the latest n entries of a subset of documents in a collection. Here's a simplified example without grouping (which you would be doing between stages 4 and 5 in this case).

This returns the latest 20 entries (based on a field called "timestamp"), sorted ascending. It then projects each documents _id, timestamp and whatever_field_you_want_to_show into the results.

var pipeline = [
        {
            "$match": { //stage 1: filter out a subset
                "first_field": "needs to have this value",
                "second_field": "needs to be this"
            }
        },
        {
            "$sort": { //stage 2: sort the remainder last-first
                "timestamp": -1
            }
        },
        {
            "$limit": 20 //stage 3: keep only 20 of the descending order subset
        },
        {
            "$sort": {
                "rt": 1 //stage 4: sort back to ascending order
            }
        },
        {
            "$project": { //stage 5: add any fields you want to show in your results
                "_id": 1,
                "timestamp" : 1,
                "whatever_field_you_want_to_show": 1
            }
        }
    ]

yourcollection.aggregate(pipeline, function resultCallBack(err, result) {
  // account for (err)
  // do something with (result)
}

so, result would look something like:

{ 
    "_id" : ObjectId("5ac5b878a1deg18asdafb060"),
    "timestamp" : "2018-04-05T05:47:37.045Z",
    "whatever_field_you_want_to_show" : -3.46000003814697
}
{ 
    "_id" : ObjectId("5ac5b878a1de1adsweafb05f"),
    "timestamp" : "2018-04-05T05:47:38.187Z",
    "whatever_field_you_want_to_show" : -4.13000011444092
}

Hope this helps.

Languet

THANK YOU @lauri108! Of all the answers to this and related questions, this is THE ONLY working and reliable solution to "how to get the LAST N DOCS". And simple enough to do in one query. Job done. – Rainstorm

Answer 7

You can try this method:

Get the total number of records in the collection with

db.dbcollection.count() 

Then use skip:

db.dbcollection.find().skip(db.dbcollection.count() - 1).pretty()

Terceira

Very bad solution according to the performance – Hollishollister

Answer 8

You can't "skip" based on the size of the collection, because it will not take the query conditions into account.

The correct solution is to sort from the desired end-point, limit the size of the result set, then adjust the order of the results if necessary.

Here is an example, based on real-world code.

var query = collection.find( { conditions } ).sort({$natural : -1}).limit(N);

query.exec(function(err, results) {
    if (err) { 
    }
    else if (results.length == 0) {
    }
    else {
        results.reverse(); // put the results into the desired order
        results.forEach(function(result) {
            // do something with each result
        });
    }
});

Greengrocery

Nice workaround! Would be nice to be able to do the same at the query level though. Something like var query = collection.find( { conditions } ).sort({$natural : -1}).reverse().limit(N). – Ansela

how can I use the same in springboot? – Predesignate

Answer 9

you can use sort() , limit() ,skip() to get last N record start from any skipped value

db.collections.find().sort(key:value).limit(int value).skip(some int value);

Fear

Answer 10

Look under Querying: Sorting and Natural Order, http://www.mongodb.org/display/DOCS/Sorting+and+Natural+Order as well as sort() under Cursor Methods http://www.mongodb.org/display/DOCS/Advanced+Queries

Ressler

Thanks for your anwser, it is close,but I want to retured records ordered from less recent to most recent, is it possible? – Huoh

Natural order should not be relied upon; if you are using a replica set (and you should be), different nodes might well have the same documents stored in a different order on disk. – Ingrowth

If you want to get the most recent records, you will have to rely on a date field in the document. – Ingrowth

Answer 11

You may want to be using the find options : http://docs.meteor.com/api/collections.html#Mongo-Collection-find

db.collection.find({}, {sort: {createdAt: -1}, skip:2, limit: 18}).fetch();

Pathogenesis

Answer 12

Use .sort() and .limit() for that

Use Sort in ascending or descending order and then use limit

db.collection.find({}).sort({ any_field: -1 }).limit(last_n_records);

Stovepipe

Answer 13

If you use MongoDB compass, you can use sort filed to filter,

Arreola

Answer 14

db.collection.find().hint( { $natural : -1 } ).sort(field: 1/-1).limit(n)

according to mongoDB Documentation:

You can specify { $natural : 1 } to force the query to perform a forwards collection scan.

You can also specify { $natural : -1 } to force the query to perform a reverse collection scan.

Plumley

Answer 15

use $slice operator to limit array elements

GeoLocation.find({},{name: 1, geolocation:{$slice: -5}})
    .then((result) => {
      res.json(result);
    })
    .catch((err) => {
      res.status(500).json({ success: false, msg: `Something went wrong. ${err}` });
});

where geolocation is array of data, from that we get last 5 record.

Door

Answer 16

Last function should be sort, not limit.

Example:

db.testcollection.find().limit(3).sort({timestamp:-1}); 

Estheresthesia

This seems incorrect. Why would sort be after limit? – Retrieval