MongoDB query to replace NULL values

Question

I'm currently working with a MongoDB database and I have fields that have a value of NULL is there a way to run a query that will replace these NULL fields with a value of "Missing" instead? An example of the document is:

{
        "_id" : 1,
        "Users" : [
                {
                        "name" : "John Davies",
                        "age" : null,
                        "place_of_birth" : "Cardigan"
                },
                {
                        "name" : "Edward Jones",
                        "age" : null,
                        "place_of_birth" : null
                },
                {
                        "name" : "Daniel Rhys",
                        "age" : NumberLong(63),
                        "place_of_birth" : "Cardigan"
                },
                {
                        "name" : null,
                        "age" : NumberLong(61),
                        "place_of_birth" : "Cardigan"
                },
                {
                        "name" : "John Davies ",
                        "age" : null,
                        "place_of_birth" : "Cardigan"
                }
        ]
}

Is this a one-time activity?

Tushar Gupta - curioustushar
– Tushar Gupta - curioustushar

2021-04-26 13:47:30 +00:00
Commented Apr 26, 2021 at 13:47 — Tushar Gupta - curioustushar
– Tushar Gupta - curioustushar, Commented Apr 26, 2021 at 13:47
Yes, I would only want to do this once

PearDrop
– PearDrop

2021-04-26 13:50:03 +00:00
Commented Apr 26, 2021 at 13:50 — PearDrop
– PearDrop, Commented Apr 26, 2021 at 13:50

Tushar Gupta - curioustushar · Accepted Answer · 2021-04-26 14:15:04Z

Demo - https://mongoplayground.net/p/dsI5G6zfbLr

Use $[]

db.collection.update(
    {},
    { $set: { "Users.$[u].age": "missing" } },
    { arrayFilters: [ { "u.age": null } ], multi: true}
)

Combine multiple queries into 1 using db.collection.bulkWrite

db.collection.bulkWrite( [
   { updateMany :
      {
         "filter": {},
         "update": { $set: { "Users.$[u].age": "missing" } },
         "arrayFilters": [ { "u.age": null } ],
      }
   },
   { updateMany :
      {
         "filter": {},
         "update": { $set: { "Users.$[u].name": "missing" } },
         "arrayFilters": [ { "u.name": null } ],
      }
   },
   { updateMany :
      {
         "filter": {},
         "update": { $set: { "Users.$[u].place_of_birth": "missing" } },
         "arrayFilters": [ { "u.place_of_birth": null } ],
      }
   }
] )

Update for MongoDB Version 3.2+

while (db.collection.find({$or:[{"Users.age":null},{"Users.name":null},{"Users.place_of_birth":null}]}).count()) {
   db.collection.bulkWrite( [
       { updateMany :
          {
             "filter": { "Users.age": null },
             "update": { $set: { "Users.$.age": "missing" } }
           }
       },
       { updateMany :
          {
             "filter": { "Users.name": null },
             "update": { $set: { "Users.$.name": "missing" } },
          }
       },
       { updateMany :
          {
             "filter": { "Users.place_of_birth": null },
             "update": { $set: { "Users.$.place_of_birth": "missing" } },
          }
       }
    ] )
}

turivishal · Accepted Answer · 2021-04-26 13:51:43Z

3

Try update with aggregation pipeline starting from MongoDB 4.2,

$map to iterate loop of Users array
$objectToArray to convert current object in $map to array key-value pair
$map to iterate loop of above converted array
$ifNull to check if value is null then replace Missing otherwise remain same
$arrayToObject convert above key-value array to object format

db.collection.update({},
  [{
    $set: {
      Users: {
        $map: {
          input: "$Users",
          in: {
            $arrayToObject: {
              $map: {
                input: { $objectToArray: "$$this" },
                in: {
                  k: "$$this.k",
                  v: { $ifNull: ["$$this.v", "Missing"] }
                }
              }
            }
          }
        }
      }
    }
  }],
  { multi: true }
)

Playground

MongoDB version 3.2 or above:

set default value for replacement in variable nullReplace
find() query to get all documents from your collection and loop through forEach
for loop of user object and check condition if value is null then replace nullReplace variable
return user oibject
updateOne() to update updated Users array in your collection

var nullReplace = "Missing";
db.collection.find({}).forEach(function(doc) {
    var Users = doc.Users.map(function(u) {
        for (var u in userObj) {
            if (userObj[u] === null) userObj[u] = nullReplace;
        }
        return userObj;
    })
    db.collection.updateOne({ _id: doc._id }, { $set: { Users: Users } });
});

edited Apr 26, 2021 at 13:51

answered Apr 26, 2021 at 13:37

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

2 Comments

PearDrop Over a year ago

I am using server version is MongoDB server version: 3.2 is there a way of doing this for that particular version?

turivishal Over a year ago

@PearDrop that is the only way in 3.2 i have added at last in answer.

Collectives™ on Stack Overflow

MongoDB query to replace NULL values

2 Answers 2

Update for MongoDB Version 3.2+

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update for MongoDB Version 3.2+

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related