MongoDB upsert inside array

Question

I am trying to insert, update, delete value in the mongoDB array. My mongoDB version is 3.4.23.

Here is my collection:

{
  "link": "abc.com",
  "Values": [{
      "valueID": "v1",
      "date": "05-07-2015",
      "value": "10"
  }]
}

I am trying to make some upsert kind of query to insert/update the Values array data but not able to do so. I got this solution but it did not work for me.

Lets say I have "valueID": "v1"

{
    "valueID": "v1",
    "date": "05-07-2015",
    "value": "20"
}

So in this case I want set "value": "20" in above collection so output should be:

{
  "link": "abc.com",
  "Values": [{
      "valueID": "v1",
      "date": "05-07-2015",
      "value": "20"//updated value
  }]
}

And if I pass "valueID": "v2" then it should insert new value into collection like this:

{
  "link": "abc.com",
  "Values": [{
      "valueID": "v1",
      "date": "05-07-2015",
      "value": "10"
  },
  {
    "valueID": "v2",
    "date": "xyz",
    "value": "xyz"
  }]
}

Your help will be highly appreciate.

Answer 1

Unfortunately, this cannot be done in a single update statement using an upsert or similar.

This can be achieved using bulkWrite() which is supported from MongoDB 3.2 onwards.

You can also use the new update() command found in MongoDB 4.2 - note this is only available in 4.2.

The update command now allows multiple update statements. While you may have to run two update commands, you only need to send the command once and have MongoDB batch the statements for you.

Given your requirement, you can try the following which will first try and update the relevant element in the array using the $ positional operator.

We then use the $addToSet array operator which will attempt to add a new array element - only if no matching array element is found perfect for our scenario where no update could be done in step 1.

Both solutions work for your scenario.

Using bulkWrite()

db.getCollection("tests").bulkWrite([
    {
        updateOne: {
            filter: {link: "abc.com", "Values.valueID": "v2"},
            update: {$set: {"Values.$.value": "xyz"}}
        }
    },
    {
        updateOne: {
            filter: {link: "abc.com"},
            update: {
                $addToSet: {
                    "Values": {
                        "valueID": "v2",
                        "date": "05-07-2015",
                        "value": "xyz"
                    }
                }
            }
        }
    }
]);

Using new update() command:

db.runCommand(
            {
                update: "tests",
                updates: [
                    {
                        q: {link: "abc.com", "Values.valueID": "v2"},
                        u: {$set: {"Values.$.value": "xyz"}}
                    },
                    {
                        q: {link: "abc.com"},
                        u: {
                            $addToSet: {
                                "Values": {
                                    "valueID": "v2",
                                    "date": "05-07-2015",
                                    "value": "xyz"
                                }
                            }
                        }
                    }
                ],
                ordered: false,
                writeConcern: {w: "majority", wtimeout: 5000}
            }
        )

Sample Data:

db.getCollection("tests").insertMany([{
        "link": "abc.com",
        "Values": [
            {
                "valueID": "v1",
                "date": "05-07-2015",
                "value": "10"
            }
        ]
    },
    {
        "link": "def.com",
        "Values": [
            {
                "valueID": "v1",
                "date": "05-07-2015",
                "value": "1"
            }
        ]
    }]
);

Insert array when none exists:

db.runCommand(
            {
                update: "tests",
                updates: [
                    {
                        q: {link: "abc.com", "Values.valueID": "v2"},
                        u: {$set: {"Values.$.value": "xyz"}}
                    },
                    {
                        q: {link: "abc.com"},
                        u: {
                            $addToSet: {
                                "Values": {
                                    "valueID": "v2",
                                    "date": "05-07-2015",
                                    "value": "xyz"
                                }
                            }
                        }
                    }
                ],
                ordered: false,
                writeConcern: {w: "majority", wtimeout: 5000}
            }
        )

Result:

    { 
        "_id" : ObjectId("5dd8164969f4361ce9821b88"), 
        "link" : "abc.com", 
        "Values" : [
            {
                "valueID" : "v1", 
                "date" : "05-07-2015", 
                "value" : "20"
            }, 
            {
                "valueID" : "v2", 
                "date" : "05-07-2015", 
                "value" : "xyz"
            }
        ]
    }    

Update an existing value:

db.runCommand(
            {
                update: "tests",
                updates: [
                    {
                        q: {link: "abc.com", "Values.valueID": "v2"},
                        u: {$set: {"Values.$.value": "new value"}}
                    },
                    {
                        q: {link: "abc.com"},
                        u: {
                            $addToSet: {
                                "Values": {
                                    "valueID": "v2",
                                    "date": "05-07-2015",
                                    "value": "new value"
                                }
                            }
                        }
                    }
                ],
                ordered: false,
                writeConcern: {w: "majority", wtimeout: 5000}
            }
        )

Result:

{ 
    "_id" : ObjectId("5dd8164969f4361ce9821b88"), 
    "link" : "abc.com", 
    "Values" : [
        {
            "valueID" : "v1", 
            "date" : "05-07-2015", 
            "value" : "20"
        }, 
        {
            "valueID" : "v2", 
            "date" : "05-07-2015", 
            "value" : "new value"
        }
    ]
}

Answer 2

Mongodb 4.2

Update with Aggregation Pipeline

Starting in MongoDB 4.2, the db.collection.update() method can accept an aggregation pipeline [ stage1, stage2, ... ] that specifies the modifications to perform.

So here $reduce operator with some $condition can do the trick.

db.getCollection("ashish2").update(
  //filter criteria for the update operation
  { },
  [{ "$set": {
    "Values": {
      "$reduce": {
        "input": "$Values",
        "initialValue": [],
        "in": {
          "$concatArrays": [
            "$$value",
            {
              "$cond": [
                // Here you can select your value with which you want to match
                { "$eq": ["$$this.valueID", "v2"] },
                // Updation after match
                [{ "valueID": "v1", "date": "05-07-2015", "value": "20" }], 
                // Updation when doesn't match
                {
                  "$concatArrays": [
                    [{ "valueID": "v1", "date": "05-07-2015", "value": "20" }],
                    ["$$this"]
                  ]
                }
              ]
            }
          ]
        }
      }
    }
  }}]
)

Note: For the privous versions you either have to use two queries or you can have a look at this answer Can you specify a key for $addToSet in Mongo?