MongoDB - Update or Insert object in array

Question

I have the following collection

{
    "_id" : ObjectId("57315ba4846dd82425ca2408"),
    "myarray" : [ 
        {
            userId : ObjectId("570ca5e48dbe673802c2d035"),
            point : 5
        },
        {
            userId : ObjectId("613ca5e48dbe673802c2d521"),
            point : 2
        },        
     ]
}

These are my questions

I want to push into myarray if userId doesn't exist, it should be appended to myarray. If userId exists, it should be updated to point.

I found this

db.collection.update({
    _id : ObjectId("57315ba4846dd82425ca2408"),
    "myarray.userId" :  ObjectId("570ca5e48dbe673802c2d035")
}, {
    $set: { "myarray.$.point": 10 }
})

But if userId doesn't exist, nothing happens.

and

db.collection.update({
    _id : ObjectId("57315ba4846dd82425ca2408")
}, {
  $push: {
      "myarray": {
          userId: ObjectId("570ca5e48dbe673802c2d035"),
          point: 10
      }
  }
})

But if userId object already exists, it will push again.

What is the best way to do this in MongoDB?

Answer 1

Try this

db.collection.update(
    { _id : ObjectId("57315ba4846dd82425ca2408")},
    { $pull: {"myarray.userId": ObjectId("570ca5e48dbe673802c2d035")}}
)
db.collection.update(
    { _id : ObjectId("57315ba4846dd82425ca2408")},
    { $push: {"myarray": {
        userId:ObjectId("570ca5e48dbe673802c2d035"),
        point: 10
    }}
)

Explanation:
In the first statement $pull removes the element with userId= ObjectId("570ca5e48dbe673802c2d035") from the array on the document where _id = ObjectId("57315ba4846dd82425ca2408").

In the second one, $push inserts this object { userId:ObjectId("570ca5e48dbe673802c2d035"), point: 10 } in the same array.

Answer 2

There is a option called update documents with aggregation pipeline starting from MongoDB v4.2,

• check condition $cond if userId in myarray.userId or not
• if yes then $map to iterate loop of myarray array and check condition if userId match then merge with new document using $mergeObjects
• if no then $concatArrays to concat new object and myarray

let _id = ObjectId("57315ba4846dd82425ca2408");
let updateDoc = {
  userId: ObjectId("570ca5e48dbe673802c2d035"),
  point: 10
};

db.collection.update(
  { _id: _id },
  [{
    $set: {
      myarray: {
        $cond: [
          { $in: [updateDoc.userId, "$myarray.userId"] },
          {
            $map: {
              input: "$myarray",
              in: {
                $mergeObjects: [
                  "$$this",
                  {
                    $cond: [
                      { $eq: ["$$this.userId", updateDoc.userId] },
                      updateDoc,
                      {}
                    ]
                  }
                ]
              }
            }
          },
          { $concatArrays: ["$myarray", [updateDoc]] }
        ]
      }
    }
  }]
)

Playground

Answer 3

Possible solution with aggregation pipeline:

db.collection.update(
      { _id },
      [
        {
          $set: {
            myarray: { $filter: {
              input: '$myarray',
              as: 'myarray',
              cond: { $ne: ['$$myarray.userId', ObjectId('570ca5e48dbe673802c2d035')] },
            } },
          },
        },
        {
          $set: {
            myarray: {
              $concatArrays: [
                '$myarray',
                [{ userId: ObjectId('570ca5e48dbe673802c2d035'), point: 10 },
                ],
              ],
            },
          },
        },
      ],
    );

We use 2 stages:

1. filter myarray (= remove element if userId exist)
2. concat filtered myarray with new element;

Answer 4

You could use a variation of the .forEach/.updateOne method I currently use in mongosh CLI to do things like that. In the .forEach, you might be able to set all of your if/then conditions that you mentioned.

Example of .forEach/.updateOne:

let medications = db.medications.aggregate([
  {$match: {patient_id: {$exists: true}}}
]).toArray();

medications.forEach(med => {
  try {
    db.patients.updateOne({patient_id: med.patient_id}, 
      {$push: {medications: med}}
    )
  } catch {
    console.log("Didn't find match for patient_id.  Could not add this med to a patient.")
  }  
})

This may not be the most "MongoDB way" to do it, but it definitely works and gives you the freedom of javascript to do things within the .forEach.

Answer 5

I haven't found any solutions based on a one atomic query. Instead there are 3 ways based on a sequence of two queries:

1. always $pull (to remove the item from array), then $push (to add the updated item to array)

   db.collection.update(
                  { _id : ObjectId("57315ba4846dd82425ca2408")},
                  { $pull: {"myarray.userId": ObjectId("570ca5e48dbe673802c2d035")}}
   )
   
   db.collection.update(
                  { _id : ObjectId("57315ba4846dd82425ca2408")},
                  {
                    $push: {
                             "myarray": {
                                         userId:ObjectId("570ca5e48dbe673802c2d035"),
                                         point: 10
                                        }
                            }
                   }
   )
   

2. try to $set (to update the item in array if exists), then get the result and check if the updating operation successed or if a $push needs (to insert the item)

   var result = db.collection.update(
       {
          _id : ObjectId("57315ba4846dd82425ca2408"),
         "myarray.userId": ObjectId("570ca5e48dbe673802c2d035")
       },
       {
          $set: {"myarray.$.point": {point: 10}}
       }
    );
   
   if(!result.nMatched){
          db.collection.update({_id: ObjectId("57315ba4846dd82425ca2408")},
                               {
                                 $addToSet: {
                                              myarray: {
                                                 userId: ObjectId("570ca5e48dbe673802c2d035"),
                                                 point: 10
                                             }
                               }
          );
   

3. always $addToSet (to add the item if not exists), then always $set to update the item in array

      db.collection.update({_id: ObjectId("57315ba4846dd82425ca2408")},
                            myarray: { $not: { $elemMatch: {userId: ObjectId("570ca5e48dbe673802c2d035")} } } },
                           { 
                              $addToSet : {
                                            myarray: {
                                                       userId: ObjectId("570ca5e48dbe673802c2d035"),
                                                       point: 10
                                                      }
                                           }
                            },
                           { multi: false, upsert: false});
   
      db.collection.update({
                             _id: ObjectId("57315ba4846dd82425ca2408"),
                              "myArray.userId": ObjectId("570ca5e48dbe673802c2d035")
                           },
                           { $set : { myArray.$.point: 10 } },
                           { multi: false, upsert: false});
   

1st and 2nd way are unsafe, so transaction must be established to avoid two concurrent requests could push the same item generating a duplicate.

3rd way is safer. the $addToSet adds only if the item doesn't exist, otherwise nothing happens. In case of two concurrent requests, only one of them adds the missing item to the array.

Answer 6

array update and create don't mix in under one query, if you care much about atomicity then there's this solution:

normalise your schema to,

{
    "_id" : ObjectId("57315ba4846dd82425ca2408"),
    userId : ObjectId("570ca5e48dbe673802c2d035"),
    point : 5
}

Answer 7

When you want update or insert value in array try it

Object in db

key:name,
key1:name1,
arr:[
    {
        val:1,
        val2:1
    }
]

Query

var query = {
    $inc:{
        "arr.0.val": 2,
        "arr.0.val2": 2
    }
}
.updateOne( { "key": name }, query, { upsert: true }

key:name,
key1:name1,
arr:[
    {
        val:3,
        val2:3
    }
]

Answer 8

The accepted answer by Flying Fisher is that the existing record will first be deleted, and then it will be pushed again.

A safer approach (common sense) would be to try to update the record first, and if that did not find a match, insert it, like so:

// first try to overwrite existing value
var result = db.collection.update(
   {
       _id : ObjectId("57315ba4846dd82425ca2408"),
       "myarray.userId": ObjectId("570ca5e48dbe673802c2d035")
   },
   {
       $set: {"myarray.$.point": {point: 10}}
   }
);
// you probably need to modify the following if-statement to some async callback
// checking depending on your server-side code and mongodb-driver
if(!result.nMatched)
{
    // record not found, so create a new entry
    // this can be done using $addToSet:
    db.collection.update(
        {
            _id: ObjectId("57315ba4846dd82425ca2408")
        },
        {
            $addToSet: {
                myarray: {
                    userId: ObjectId("570ca5e48dbe673802c2d035"),
                    point: 10
                }
            }
        }
    );
    // OR (the equivalent) using $push:
    db.collection.update(
        {
            _id: ObjectId("57315ba4846dd82425ca2408"),
            "myarray.userId": {$ne: ObjectId("570ca5e48dbe673802c2d035"}}
        },
        {
            $push: {
                myarray: {
                    userId: ObjectId("570ca5e48dbe673802c2d035"),
                    point: 10
                }
            }
        }
    );
}

This should also give (common sense, untested) an increase in performance, if in most cases the record already exists, only the first query will be executed.

Answer 9

Unfortunately "upsert" operation is not possible on embedded array. Operators simply do not exist so that this is not possible in a single statement.Hence you must perform two update operations in order to do what you want. Also the order of application for these two updates is important to get desired result.