How to delete N numbers of documents in mongodb

Question

In my collections, documents contains key like status and timestamp. When I want to find latest ten documents then I write following query

db.collectionsname.find().sort({"timestamp"-1}).limit(10)

This query gives me results which I want but when I want to delete latest ten documents then I was writing the following query

db.collectionsname.remove({"status":0},10).sort({"timestamp":-1})

but it shows following error TypeError: Cannot call method 'sort' of undefined and again I wrote the same query as below db.collectionsname.remove({"status":0},10) It deletes only one document. So how can I write a query which deletes ten latest documents and sorts on timestamp?

Answer 1

You can't set a limit when using remove or findAndModify. So, if you want to precisely limit the number of documents removed, you'll need to do it in two steps.

db.collectionName.find({}, {_id : 1})
    .limit(100)
    .sort({timestamp:-1})
    .toArray()
    .map(function(doc) { return doc._id; });  // Pull out just the _ids

Then pass the returned _ids to the remove method:

db.collectionName.remove({_id: {$in: removeIdsArray}})

FYI: you cannot remove documents from a capped collection.

Answer 2

You can pipeline the output of find query mapping to use _id and performing a remove based on $in query such as:

db.collection.remove({_id: 
    { $in: db.collection.find().sort({timestamp:-1}).limit(100).map(a => a._id) }
})

Answer 3

Let N be number of records to delete.

    db.collectionName.find().limit(N).forEach(doc => 
     { 
       db.collectionName.remove({_id:doc._id})
     }
    )

Answer 4

To remove N number of documents in your collection myCollection:

db.getCollection('myCollection').find({}).limit(N).forEach(function(doc){
    db.getCollection('myCollection').remove({_id: doc._id});
})

Answer 5

Working solution (Inspired by answers above):

(For deleting Huge amount of records the $in operator has a limitation so this is the ultimate solution) Example for deleting 1,000,000 records

var ITERATIONS=1000;
var LIMIT=1000;
for(i=0; i<ITERATIONS; i++) {
    arr = db.MyCollection.find({}, {_id : 1}).limit(LIMIT).toArray().map(function(doc) { return doc._id; });
    db.MyCollection.remove({_id: {$in: arr}});
    // sleep(1);  // Optional for not loading mongo in case of huge amount of deletions
}

You can run this in mongo shell or in Robomongo 3T

Answer 6

Here is another approach:

Step 1: Sort and limit and $out documents in temporary collection

db.my_coll.aggregate([
  { $sort: { _id: - 1} },
  { $limit: 10000 },
  { $out: 'my_coll_temp' }
])

Step 2: Remove (Not dropping since, this preserves indexes) all the documents from the original collection. Also this step is what might make this approach less versatile since, removing might not always be possible.

db.my_coll.remove({})

Step 3: Move back the documents from temporary collection to original collection

db.my_coll_temp.aggregate([ { $out: 'my_coll' } ])

Step 4: Drop the temporary collection

db.my_coll_temp.drop()

Answer 7

The way I find is:

        let uid = "your_user";

        let a = await db
          .collection(`notifications`)
          .find({
            owner: uid,
          })
          .sort({ _id: -1 })
          .skip(1000)
          .limit(1)
          .toArray();

        if (a.length > 0) {
          let _id = new ObjectId(a[0]._id);
          db.collection(
            `notifications`
          ).deleteMany({
            owner: uid,
            _id: { $lt: _id },
          });
        }

Explanation: The 'a' variable get the _id of the last entry you want to delete olders. You can specify what n value by using 'skip'. Example: You have 5000 entries but you wanna stay with 400 new ones, just set skip to '400'. In this case we are sorting by _id, but you can use what you want. You can use find to filter results to match exactly what you need.

The second half of algorithm do the delete. Use the same filter (owner:uid) to match the first find, and delete many less than "_id".

You also use a 'count' if statement before this code block to check if it is necessary, to avoid server coasts.

Answer 8

Another way is to write a python script.

from pymongo import MongoClient

def main():
    local_client = MongoClient()
    collection = local_client.database.collection
    cursor = collection.find()
    total_number_of_records = 10000

    for document in cursor:
        id = document.get("_id")

        if total_number_of_records == 100:
            break

        delete_query = {"_id": id}
        collection.delete_one(delete_query)

        total_number_of_records -= 1

if __name__ == "__main__":
    # execute only if run as a script
    main()

Answer 9

Below query will find and delete the latest 10 documents from collection:-

db.collectionsname.findAndModify({
    query: { 'status':0 },
    sort: { 'timestamp': -1 },
    limit: 10,
    remove: true
});

Answer 10

query sql is

db.order.find({"业务员姓名" : "吊炸天"},{"业务员编号":0}).sort({ "订单时间" : -1 })

the result is

{
"_id" : ObjectId("5c9c875fdadfd961b4d847e7"),
"推送ID" : "248437",
"订单时间" : ISODate("2019-03-28T08:35:52Z"),
"订单状态" : "1",
"订单编号" : "20190328163552306694",
"业务员姓名" : "吊炸天"
}
{
"_id" : ObjectId("5c9c875fdadfd961b4d847e8"),
"推送ID" : "248438",
"订单时间" : ISODate("2019-03-28T08:35:52Z"),
"订单状态" : "1",
"订单编号" : "20190328163552178132",
"业务员姓名" : "吊炸天"
}
{
"_id" : ObjectId("5c9c875fdadfd961b4d847e5"),
"推送ID" : "248435",
"订单时间" : ISODate("2019-03-28T08:35:51Z"),
"订单状态" : "1",
"订单编号" : "20190328163551711074",
"业务员姓名" : "吊炸天"
}
{
"_id" : ObjectId("5c9c875fdadfd961b4d847e6"),
"推送ID" : "248436",
"订单时间" : ISODate("2019-03-28T08:35:51Z"),
"订单状态" : "1",
"订单编号" : "20190328163551758179",
"业务员姓名" : "吊炸天"
}

now delete the 3 and 4 data

var name = ["吊炸天"]
var idArray = db.order.find({"业务员姓名" : {$in:name}},{"订单编号":1,})
                .sort({ "订单时间" : -1 })
                .skip(2)
                .map(function(doc){return doc.订单编号})

db.order.deleteMany({"订单编号":{$in:idArray}})

return result is

{
"acknowledged" : true,
"deletedCount" : 2
}