How to empty fields in mongodb except last ones

Working with Data
1

I have a mongo database in which i need after several insert operations, to empty a specif field in all documents excepts last two documents created:

db.getCollection('test').updateMany({}, {\$set:{test:undefined}})

The query above empty field “test” in all documents.

What i want is to exclude last two created documents.

2

First, look at https://docs.mongodb.com/manual/reference/operator/update/unset/.

Do you not how to uniquely identify the last 2 documents?

Rather than

updateMany({},...)

use

updateMany( { $expr : { $not : { Condition-That-Select-The-Last-2-Documents } } }, ... )
3

can i exclude them like this :

db.test.find().sort({"_id":-1}).limit(2)
4

No. But you can keep the _id in a variable and use $nin. Something along the lines

ids_to_exclude = db.test.find().sort({"_id":-1}).limit(2)
... updateMany( { _id : { $nin : ids_to_exclude } }  , ... )
5

i got “$nin needs an array”

6

What about

updateMany( { _id : { $nin : list(ids_to_exclude) } }  , ... )
7

i tried this :

 ids_to_exclude = db.test.find({},{_id:1}).sort({"_id":-1}).limit(2).toArray();

i get this result :

[
    {
            "_id" : ObjectId("611c336270a39c00b7fd1f5e")
    },
    {
            "_id" : ObjectId("611b96a6699005a8f561fe52")
    }

]
the problem now is that nothing is being excluded i thnik my array should look like this:

["611c336270a39c00b7fd1f5e","611b96a6699005a8f561fe52"]
8

With the

ids_to_exclude = ...toArray()

at the end you should be okay to use $nin:ids_to_exclude

9

already did that but now every document is updated the last two are not excluded

10

Can you show the exact command you are doing?

11

i solved the problem like this:

ids_to_exclude = db.test.find({},{_id:1}).sort({"_id":-1}).limit(2).toArray();

var array = new Array();

ids_to_exclude.forEach(function(myDoc){array.push(myDoc._id ); } );

db.getCollection('test').updateMany({ _id : { $nin : array } }, {$set:{test:undefined}})

Thanks a lot for you help :smiley:

1 Like
12

Good.

Yes, you were right. The output of find()…toArray() was an array of object and you needed an array of ObjectId(). Nice job!

1 Like
13

Now i have a little problem when trying this in shell script ( works fine directly on mongo database shell):

mongo jenkinsdb<< EOF


ids_to_exclude = db.history.find({projectId: $object_id._id.str}, 
{_id:1}).sort({"_id":-1}).limit(2).toArray()
var array = new Array()
ids_to_exclude.forEach(function(myDoc){array.push(myDoc._id);})
array
db.getCollection('history').updateMany({ _id:{$nin:array}},{$set:{backupPath:undefined}})

exit
EOF

I get “uncaught exception: SyntaxError: expected property name, got ‘:’ :
@(shell):1:46” i think the problem is in “db.getCollection …” command.

14

Hi @youssef_boudaya

Bash is expanding the $commads as bash variables. To avoid that put the delimiter of your heredoc in quotes.

mongo jenkinsdb<< "EOF" alternatively you can escape each $: \$

2 Likes
16

i tried this :

object_id=$(<object_id.json)


mongo jenkinsdb<< EOF
ids_to_exclude = db.history.find({projectId: $object_id._id.str}, 
{_id:1}).sort({"_id":-1}).limit(2).toArray()
var array = new Array()
ids_to_exclude.forEach(function(myDoc){array.push(myDoc._id);})
array
db.getCollection('history').updateMany({ _id:{\$nin:array}},{\$set:{backupPath:undefined}})
EOF

The last two documents are now excluded from the update operation.
I want to know is the best way to empty field is to set it as undefined ?

17

There are a few problems with this execution, starting with the variable creation in bash, inspect the variable and you will see what I mean.

I would approach the problem using mongosh in this manner:

// create testbed
db.foo.drop()
var bo = db.foo.initializeOrderedBulkOp()
for(i=0;i<10000;i++){bo.insert({n:i, backupPath:'/somepath/'+i });}
bo.execute()
db.foo.countDocuments()
db.foo.find().sort({_id:-1},{backupPath:1})

// delete all but the last two, relies on _id being an ObjectID
var secondLast = db.foo.find().sort({_id: -1}).limit(2).toArray()[1]._id
// for a design that can handle it, flexibleSchema design
db.foo.updateMany({_id: {$lt: secondLast}},{$unset:{backupPath:""}})
// otherwise
// db.foo.updateMany({_id: {$lt: secondLast}},{$set:{backupPath: null}})
db.foo.find().sort({_id:-1},{backupPath:1})
1 Like