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.

give the bulkWrite method mentioned [here](https://stackoverflow.com/a/57726796/4368485) a try — Dĵ ΝιΓΞΗΛψΚ, Nov 22 '19 at 10:03

Samuel Goldenbaum · Answer 1 · 2019-11-22T17:59:12.743

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

Sure, make your you running v 4.2. I updated the answer to include sample data and results. — Samuel Goldenbaum, Nov 22 '19 at 17:25
Ohh seems like versioning issue I may face here. Btw I will try same with v 4.2. — Saurabh Agrawal, Nov 25 '19 at 05:02

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

3

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?

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 22 '19 at 08:26

Ashh

44,693
14
105
132

It's saying `code: 14 codeName: "TypeMismatch" errmsg: "Wrong type for 'u'. Expected a object, got a array." name: "MongoError"`. My mongoDB version is 3.4.23 – Saurabh Agrawal Nov 22 '19 at 09:01
@SaurabhAgrawal Are you sure you are on mongodb 4.2? – Ashh Nov 22 '19 at 09:29
No it's ` 3.4.23 ` – Saurabh Agrawal Nov 22 '19 at 09:33
@SaurabhAgrawal I already mentioned in the answer you should have atleast **4.2**. – Ashh Nov 22 '19 at 09:38
From my experience, this one doesn't insert if the array is empty and will insert an object each time it doesn't match ( instead of insert only once ). It seems to only work when you want to update an array containing only one object and that you match with it. Couldn't make this one work while I had no issue with the answer from @Ashh – AlaricB Oct 22 '20 at 14:37

score 0 · Answer 3 · answered Nov 22 '19 at 10:28

Have a look at $addToSet operator

The $addToSet operator adds a value to an array unless the value is already present, in which case $addToSet does nothing to that array.

https://docs.mongodb.com/manual/reference/operator/update/addToSet/index.html

MongoDB upsert inside array

3 Answers3

Update with Aggregation Pipeline