How to remove only rows which has null values in mongodb in a single query

Question

I have a collection test with a record :

[{ number:"1524",
name:null,
cat_id:"126842"},
{number:"456",
name:null,
cat_id:null }]

How to remove the whole row having null values like this :

[{ number:"1524",
 cat_id:"126842" },
{ number:"456"}]

I tried this but whole records are getting deleted rather than just the rows.

collection.deleteMany( { $or: [{cat_id : null },{ name : null }]

Need to remove multiple variables in a single query.

MongoDB version 4.0

Abhishek Bhagate · Answer 1 · 2020-06-04T20:15:35.733

2

In MongoDB, the $unset operator is used to delete a particular field.

You can try using the following commands to delete field that are empty

collection.updateMany({name: null}, { $unset : { name : 1 }})

This command will delete the name field from all documents where name is null. You can do a similar command for cat_id as well.

Edit : As per an answer received by me in another question I asked, You can also do the same in a single command using the following way -

collection.updateMany({
  $or : [{
    name : null
  }, {
    cat_id : null
  }]
}, [{
  $set: {
    name : { $ifNull : ["$hobby", "$$REMOVE"] },
    cat_id : { $ifNull : ["$Roll_no", "$$REMOVE"] }
  }
}]
);

However, this can only be done in MongoDb V4.2 and above. You can also look at this for another way of doing it.

Hope it helps !

edited Jun 04 '20 at 20:15

answered Jun 04 '20 at 18:48

Abhishek Bhagate

5,583
3
15
32

can we do it in a single db call ? – Jun 04 '20 at 18:48
1

I thought of mentioning it in answer, but I am not absolutely sure about it. – Abhishek Bhagate Jun 04 '20 at 18:54
can we try that with $or ? – Jun 04 '20 at 18:58
@sigmoidnuetron We can't use it in the same way. check the edit I made and see if it helps – Abhishek Bhagate Jun 04 '20 at 20:17

How to remove only rows which has null values in mongodb in a single query

1 Answers1