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"
                }
        ]
}

user_8987128 · Answer

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 } });
});

user_2224265 · Answer

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" } },
          }
       }
    ] )
}

MongoDB query to replace NULL values

2 Answers

Update for MongoDB Version 3.2+