Add a new field to all documents of a collection with the value from the document field into MongoDB-CodePudding

I'm having difficulty with adding another field to all records of the user's collection with a value from each document. I'm familiar with using $set with db.model.updateMany, and $addFields with aggregation pipeline, I've used both to solve problems in the past, in this case, I have to perform some logics/calculation before adding the value, right here is where my problem lies.

Say, I have the schema like this:

{
  "users": [
    {
      "wallets": {...},
      "avatar": "",
      "isVerified": false,
      "suspended": false,
      "country": "Nigeria",
      "_id": "123",
      "resetPasswordToken": "",
      "email": "[email protected]",
      "phone": "08012398743",
      "name": "Agbakwuru Nnaemeka Kennedy ",
      "role": "user",
    },
    {...}
}

I want to add a new field phoneNumber, that will take the value of the existing field phone, but before adding, I'd like to run a logic on it, as some of the phone values are having whitespaces most of them are not correctly formatted, and I'd like to prepend the country code to the phone value, before adding it to the new phoneNumber field.

I was able to get this done using a cursor from Mongoose db.mode.aggregate method, with $match filter, and adding the field to each document with the aggregate $addFields pipeline, this prove to take a whole lot of time, I had to stop the operation as it's taking too much time to run.

I'd like to believe there's a better way out there, please, I will appreciate any help.

Edit:

Here's the aggregation I'm using:

const userCursor = User.aggregate([{$match: {phone: {$exists: true}}}]);
for await (const doc of userCursor) {
  await User.findByIdAndUpdate(doc._id, {$set: {
          phoneNumber: convertPhoneNumber({phoneNumber: doc.phone.replace(/\s /g, "")})}
  });
}

The convertPhoneNumber is a helper method I defined in my utils to prepend the country dealing code to the phone number.

CodePudding user response：

You can use $function and call that javascript code in the database.

This requires >=MongoDB 4.4

db.Users.update(
  {phone: {$exists: true}},
  [{$set: {phoneNumber:
            {
             "$function": {
             "body": YOUR_convertPhoneNumber_FUNCTION_DEF,
             "args": ["$phoneNumber"],
             "lang": "js"
             }
            }])

Also if the code of convertPhoneNumber, can be written in MongodBD using aggregate operators you can avoid the javascript also.

The above is a pipeline update and we can use all aggregate operators when updating.

Edit

If mongoose has problem with the $function or nodejs driver method has problem with pipeline update you can also do.

db.runCommand(
   {
      update: "yourCollectionName",
      updates: [
         {
           q: {phone: {$exists: true}},
           u: 
           [{$set: {phoneNumber:
            {
             "$function": {
             "body": YOUR_convertPhoneNumber_FUNCTION_DEF,
             "args": ["$phoneNumber"],
             "lang": "js"
             }
            }],
           multi: true
         }
      ],
      ordered: false
   }
)

CodePudding user response：

I would attempt to run a script like that directly in the mongo command line, or Robo3T :

db.getCollection("users").find({}).forEach( doc => {

    doc.users.forEach( user => {

        // do your logic here
        let phoneNumber = "12345";
        phoneNumber = " 007"   phoneNumber;

        user.phoneNumber = phoneNumber;
    })

    db.users.save(doc);
})

It will still take a while on 300k documents, but give it a few minutes.

CodePudding user response：

You can try Bulk Operation, this updates the collection in batches of 1000 documents:

var bulkOperations = [];
db.getCollection("users").find({}).forEach(doc => {
   doc.users.forEach(user => {
      user.phoneNumber = convertPhoneNumber({phoneNumber: user.phone.replace(/\s /g, "")});
   })
   bulkOperations.push({
      updateOne: {
         filter: { id: doc._id },
         update: { $set: { users: doc.users } }
      }
   });
   if (bulkOperations.length > 1000) {
      db.getCollection("users").bulkWrite(bulkOperations, { ordered: false });
      bulkOperations = [];
   }
})
if (bulkOperations.length > 0) 
   db.getCollection("users").bulkWrite(bulkOperations, { ordered: false });