CODEWITHSUNDEEP
mongodbrecord
Bin Chen
Bin Chen

Reputation: 63309

How to get the last N records in mongodb?

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.

Upvotes: 698

Views: 784917

Answers (16)

Sahil Thummar
Sahil Thummar

Reputation: 2490

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);

Upvotes: 4

Ashwini
Ashwini

Reputation: 747

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.

Upvotes: 24

KARTHIKEYAN.A
KARTHIKEYAN.A

Reputation: 20088

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.

Upvotes: 1

satywan kumar
satywan kumar

Reputation: 215

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

Upvotes: 16

Ishan Liyanage
Ishan Liyanage

Reputation: 2407

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

enter image description here

Upvotes: 4

João Otero
João Otero

Reputation: 998

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/

Upvotes: 23

Ajibola Bello
Ajibola Bello

Reputation: 474

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()

Upvotes: 12

Gili.il
Gili.il

Reputation: 11

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.

Upvotes: 1

lauri108
lauri108

Reputation: 1421

@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.

Upvotes: 13

Lucbug
Lucbug

Reputation: 516

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();

Upvotes: 3

Ramesh Kasi
Ramesh Kasi

Reputation: 141

Last function should be sort, not limit.

Example:

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

Upvotes: -6

Trasplazio Garzuglio
Trasplazio Garzuglio

Reputation: 3585

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)

Upvotes: 169

Marty Hirsch
Marty Hirsch

Reputation: 671

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
        });
    }
});

Upvotes: 11

pkp
pkp

Reputation: 300

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);

Upvotes: 8

Justin Jenkins
Justin Jenkins

Reputation: 27080

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);

Upvotes: 961

Steve Wilhelm
Steve Wilhelm

Reputation: 6260

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

Upvotes: 7

Related Questions