How to delete N numbers of documents in mongodb
MongodbMongodb Problem Overview
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?
Mongodb Solutions
Solution 1 - Mongodb
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 _id
s to the remove method:
db.collectionName.remove({_id: {$in: removeIdsArray}})
FYI: you cannot remove documents from a capped collection.
Solution 2 - Mongodb
Let N be number of records to delete.
db.collectionName.find().limit(N).forEach(doc =>
{
db.collectionName.remove({_id:doc._id})
}
)
Solution 3 - Mongodb
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) }
})
Solution 4 - Mongodb
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});
})
Solution 5 - Mongodb
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
Solution 6 - Mongodb
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()
Solution 7 - Mongodb
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.
Solution 8 - Mongodb
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()
Solution 9 - Mongodb
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
}
Solution 10 - Mongodb
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
});