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;

1. for a single array element
2. 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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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 !

Answer 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.