CODEWITHSUNDEEP
mongodbmongodb-query
gilad s
gilad s

Reputation: 485

Update Unique for One Field in Array

A user collection has an array of sites. Each site has a name. I wish that all site names will be unique per user.

user-1 with two sites called "a" and "b", and user-2 with a site called "a" is ok. But user-1 with two sites called "a" and "a" is not ok.

Here is an attempt to update a site's name but only if it's unique per user. Unfortunately it doesn't work:

collection.update({ 
    _id: userId, 
    'sites._id' : req.params.id, 
    'sites' : {$not : {$elemMatch : {name: req.body.name}}}
    },                 
    { $set: { 'sites.$.name': req.body.name } });

Data example:

db.users.insert({
"_id" : ObjectId("59f1ebf8090f072ee17438f5"),
"email" : "[email protected]",
"sites" : [
    {
        "_id" : ObjectId("59f5ebf8190f074ee17438f3"),
        "name" : "site 1"
    },
    {
        "_id" : ObjectId("59f5bbf8194f074be17438f2"),
        "name" : "site 2"
    }
]});

Upvotes: 1

Views: 2476

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

So the problem here is that you need to update by a specific _id value in the array but only where the "name" does not already exist in another element. The particular problem is the positional $ operator and how that applies to using the "matched" element.

Point is you want the "position" from the _id but the other constraint on the whole array without actually affecting either the document match or the position.

You cannot use $elemMatch since the "name" test applies to ALL elements of the array, regardless of _id. And you cannot "dot notate" both fields in the query, since the match conditions could be found on different positions. For example "site 3" is not matched on the first array element, but a provided _id value may be matched on the second array element.

Presently, the positional $ operator only works with the first match found. So what needs to happen at current is we can only really keep the position from the _id match, even though you still want a constraint looking for "unique" values within "name" which are not currently present in another before altering.

Current MongoDB

The only way you currently get to keep the position but be able to test the array for not having the "name" is to use $where with a JavaScript expression which looks at the array. The trick being that the normal query conditions match the "position", but the $where affects document selection without having any effect on the "position" which is matched.

So trying to write "site 2" to the first array entry:

db.users.update(
  { 
    "_id" : ObjectId("59f1ebf8090f072ee17438f5"),
    "sites._id": ObjectId("59f5ebf8190f074ee17438f3"),
    "$where": '!this.sites.some(s => s.name === "site 2")'
  },
  {
    "$set": { "sites.$.name": "site 2" }   
  }
)

Updates nothing because the constraint of:

!this.sites.some(s => s.name === "site 2")

Shows that at least one of the array elements via Array.some() actually matched the criteria, so the document is rejected from update.

When you change to "site 3" on the second element:

db.users.update(
  { 
    "_id" : ObjectId("59f1ebf8090f072ee17438f5"),
    "sites._id": ObjectId("59f5bbf8194f074be17438f2"),
    "$where": '!this.sites.some(s => s.name === "site 3")'
  },
  {
    "$set": { "sites.$.name": "site 3" }   
  }
)

None of the array elements are matched by the $where clause, but of course the _id does so this position is used in the update. So the update applies correctly to the matched element and not just the "first element" which did not have the name:

/* 1 */
{
    "_id" : ObjectId("59f1ebf8090f072ee17438f5"),
    "email" : "[email protected]",
    "sites" : [ 
        {
            "_id" : ObjectId("59f5ebf8190f074ee17438f3"),
            "name" : "site 1"
        }, 
        {
            "_id" : ObjectId("59f5bbf8194f074be17438f2"),
            "name" : "site 3"
        }
    ]
}

MongoDB 3.6

From MongoDB 3.6 we can do this differently and not rely on JavaScript evaluation as a "workaround". The new addition is the positional filtered $[<identifier>] operator which allows us to "turn around" those conditions as above and instead "query" natively on the "name" but "filter" on the "_id" value:

db.users.update(
  {
    "_id": ObjectId("59f1ebf8090f072ee17438f5"),
    "sites.name": { "$ne": "site 4" }
  },
  { "$set": { "sites.$[el].name": "site 4" } },
  { "arrayFilters": [{ "el._id": ObjectId("59f5bbf8194f074be17438f2") }]  }
)

So here we get the correct result of the second array element still being updated despite the query condition actually meeting a "first" match on the very first element of the array. This is because what takes over after the "document selection" from the query is how the arrayFilters are defined along with the positional filtered operator. Her we alias as [el] for the matched element from the "sites" array, and look for it's _id sub-property within the arrayFilters definition.

This actually makes a lot more sense to read and is easier to construct that a JavaScript statement representing the condition. So it's a really useful boon being added into MongoDB for updating.

Conclusion

So right now, code using $where to positionally match like this yet still put a constraint on the array. From MongoDB 3.6 this will be much easier to code and a bit more efficient since the document exclusion can be fully done in native code operators whilst still retaining a method for matching the position.

Upvotes: 1

Related Questions