MongoDB conditionally $addToSet sub-document in array by specific field

Question

Is there a way to conditionally $addToSet based on a specific key field in a subdocument on an array?

Here's an example of what I mean - given the collection produced by the following sample bootstrap;

cls
db.so.remove();
db.so.insert({
    "Name": "fruitBowl",
    "pfms" : [
        {
            "n" : "apples"
        }
    ]
});

n defines a unique document key. I only want one entry with the same n value in the array at any one time. So I want to be able to update the pfms array using n so that I end up with just this;

{
    "Name": "fruitBowl",
    "pfms" : [
        {
            "n" : "apples",
            "mState": 1111234
        }
    ]
}

Here's where I am at the moment;

db.so.update({
  "Name": "fruitBowl",
},{
// not allowed to do this of course
//    "$pull": {
//  "pfms": { n: "apples" },
//    },

"$addToSet": {
  "pfms": {
    "$each": [
      {
        "n": "apples",
        "mState": 1111234
      }
    ]
   }
  }
 }
)

Unfortunately, this adds another array element;

db.so.find().toArray();

[
    {
        "Name" : "fruitBowl",
        "_id" : ObjectId("53ecfef5baca2b1079b0f97c"),
        "pfms" : [
            {
                "n" : "apples"
            },
            {
                "n" : "apples",
                "mState" : 1111234
            }
        ]
    }
]

I need to effectively upsert the apples document matching on n as the unique identifier and just set mState whether or not an entry already exists. It's a shame I can't do a $pull and $addToSet in the same document (I tried).

What I really need here is dictionary semantics, but that's not an option right now, nor is breaking out the document - can anyone come up with another way?

FWIW - the existing format is a result of language/driver serialization, I didn't choose it exactly.

further

I've gotten a little further in the case where I know the array element already exists I can do this;

db.so.update({
    "Name": "fruitBowl",
    "pfms.n": "apples",
},{
  $set: {
   "pfms.$.mState": 1111234,
  },
 }
)

But of course that only works;

for a single array element
as long as I know it exists

The first limitation isn't a disaster, but if I can't effectively upsert or combine $addToSet with the previous $set (which of course I can't) then it the only workarounds I can think of for now mean two DB round-trips.

Neil Lunn · Answer 1 · 2014-08-15T09:12:27.860

5

The $addToSet operator of course requires that the "whole" document being "added to the set" is in fact unique, so you cannot change "part" of the document or otherwise consider it to be a "partial match".

You stumbled on to your best approach using $pull to remove any element with the "key" field that would result in "duplicates", but of course you cannot modify the same path in different update operators like that.

So the closest thing you will get is issuing separate operations but also doing that with the "Bulk Operations API" which is introduced with MongoDB 2.6. This allows both to be sent to the server at the same time for the closest thing to a "contiguous" operations list you will get:

var bulk = db.so.initializeOrderedBulkOp();

bulk.find({ "Name": "fruitBowl", "pfms.n": "apples": }).updateOne({ 
    "$pull": { "pfms": { "n": "apples" } }
});
bulk.find({ "Name": "fruitBowl" }).updateOne({
    "$push": { "pfms": { "n": "apples", "state": 1111234 } }
})

bulk.execute();

That pretty much is your best approach if it is not possible or practical to move the elements to another collection and rely on "upserts" and $set in order to have the same functionality but on a collection rather than array.

edited Aug 15 '14 at 09:12

answered Aug 15 '14 at 05:51

Neil Lunn

148,042
36
346
317

Thanks - I'm using the C# driver so assuming InertBatch() is the same thing that's what I'd resolved to try this morning. It turns out that even if I could do conditional updates, the importance that MongoDB places on field ordering would negate the benefits in any case. Removing the array element first (depressingly) seems to be the only reasonable option. – cirrus Aug 15 '14 at 08:53
Oh wait - clearly I'm tired, InsertBatch isn't the same thing at all :( – cirrus Aug 15 '14 at 09:02
@cirrus The [BulkWriteRequestBuilder](http://api.mongodb.org/csharp/current/html/912d8f99-c22b-2557-23e0-e798bc920b62.htm) is the place to begin looking, along with other "bulk" related methods. – Neil Lunn Aug 15 '14 at 09:04
Thanks Neil - incidentally, I don't have initializeOrderedBulkOp() as a function in my local mongo shell - what version is that? – cirrus Aug 15 '14 at 09:08
@cirrus Bulk operations are introduced with MongoDB 2.6 and of course need that version of server at least in order to support them. The link with all the information is in the response. There are compelling reasons to think about upgrading, as apart from several fixes and improvements the returned results from these operations is much better. For instance, it is now possible to tell if an `$addToSet` operation actually "added" a new member or not. With the legacy write concern responses this is not possible. If you cannot upgrade, another collection would suit you better. – Neil Lunn Aug 15 '14 at 09:16

score 5 · Answer 2 · answered Nov 29 '17 at 10:14

I have faced the exact same scenario. I was inserting and removing likes from a post.

What I did is, using mongoose findOneAndUpdate function (which is similar to update or findAndModify function in mongodb).

The key concept is

Insert when the field is not present
Delete when the field is present

The insert is

findOneAndUpdate({ _id: theId, 'likes.userId': { $ne: theUserId }},
{ $push: { likes: { userId: theUserId, createdAt: new Date() }}},
{ 'new': true }, function(err, post) { // do the needful });

The delete is

findOneAndUpdate({ _id: theId, 'likes.userId': theUserId},
{ $pull: { likes: { userId: theUserId }}},
{ 'new': true }, function(err, post) { // do the needful });

This makes the whole operation atomic and there are no duplicates with respect to the userId field.

I hope this helpes. If you have any query, feel free to ask.

How about update data If existed? Did I miss something? – Tokenyet Jun 08 '21 at 08:48 — Tokenyet, Jun 08 '21 at 08:48

score 1 · Answer 3 · answered Jun 30 '19 at 21:05

As far as I know MongoDB now (from v 4.2) allows to use aggregation pipelines for updates. More or less elegant way to make it work (according to the question) looks like the following:

db.runCommand({
  update: "your-collection-name",
  updates: [
    {
      q: {},
      u: {
        $set: {
          "pfms.$[elem]": {
            "n":"apples",
            "mState": NumberInt(1111234)
          }
        }
      },
      arrayFilters: [
        {
          "elem.n": {
            $eq: "apples"
          }
        }
      ],
      multi: true
    }
  ]
})

score 0 · Answer 4 · answered Jun 09 '21 at 09:29

In my scenario, The data need to be init when not existed, and update the field If existed, and the data will not be deleted. If the datas have these states, you might want to try the following method.

// Mongoose, but mostly same as mongodb
    // Update the tag to user, If there existed one.
    const user = await UserModel.findOneAndUpdate(
      {
        user: userId,
        'tags.name': tag_name,
      },
      {
        $set: {
          'tags.$.description': tag_description,
        },
      }
    )
      .lean()
      .exec();

    // Add a default tag to user
    if (user == null) {
      await UserModel.findOneAndUpdate(
        {
          user: userId,
        },
        {
          $push: {
            tags: new Tag({
              name: tag_name,
              description: tag_description,
            }),
          },
        }
      );
    }

This is the most clean and fast method in the scenario.

score -1 · Answer 5 · answered Feb 08 '16 at 14:42

As a business analyst , I had the same problem and hopefully I have a solution to this after hours of investigation.

// The customer document: 

{
   "id" : "1212",
    "customerCodes" : [ 
        {
            "code" : "I"          
        },
        {
            "code" : "YK"          
        }        
    ]
}

// The problem : I want to insert dateField "01.01.2016" to customer documents where customerCodes subdocument has a document with code "YK" but does not have dateField. The final document must be as follows :

{
   "id" : "1212",
    "customerCodes" : [ 
        {
            "code" : "I"          
        },
        {
            "code" : "YK" , 
            "dateField" : "01.01.2016"
        }        
    ]
}

// The solution : the solution code is in three steps :

// PART 1 - Find the customers with customerCodes "YK" but without dateField

// PART 2 - Find the index of the subdocument with "YK" in customerCodes list.

// PART 3 - Insert the value into the document

// Here is the code

    // PART 1 
    var myCursor = db.customers.find({ customerCodes:{$elemMatch:{code:"YK", dateField:{ $exists:false} }}});

    // PART 2 
    myCursor.forEach(function(customer){       
         if(customer.customerCodes != null ) 
         {           
            var size = customer.customerCodes.length;  
            if( size > 0 )  
            {
                var iFoundTheIndexOfSubDocument= -1; 
                var index = 0;     
                customer.customerCodes.forEach( function(clazz)
                {                           
                    if(  clazz.code == "YK" && clazz.changeDate == null ) 
                    {
                        iFoundTheIndexOfSubDocument = index;                 

                    }
                    index++;  
                }) 

                // PART 3
                // What happens here is : If i found the indice of the 
                // "YK" subdocument, I create "updates" document which   
//                 corresponds to the new data to be inserted`                       
                //
                if( iFoundTheIndexOfSubDocument != -1 ) 
                {
                    var toSet = "customerCodes."+ iFoundTheIndexOfSubDocument +".dateField";
                    var updates = {};
                    updates[toSet] = "01.01.2016";

                    db.customers.update({ "id" : customer.id } , { $set: updates }); 
    // This statement is actually interpreted like this : 
    // db.customers.update({ "id" : "1212" } ,{ $set: customerCodes.0.dateField : "01.01.2016" });
                }
            }                                       
         }
    });

Have a nice day !

thanks, that approach would work. Although I was trying to do it without the extra round-trip if possible. — cirrus, Feb 08 '16 at 18:05

MongoDB conditionally $addToSet sub-document in array by specific field

5 Answers5