Friday, 21 April 2017

MongoDB Enforcing Complex constraints across documents in a collection

Lets say I have documents in a collection called Users that look like this:

{
  name: 'vic', <-- unique index
  ownedItems: ['paperclip-1252', 'stapler-1337']
}

ownedItems is an array of the uniquely indexed identifiers in some other Items collection. Now, lets say I want to add an element to the ownedItems array, but I want to enforce the rule that: one cannot update the ownedItems array of a user in a way that results in more than one User document existing that contains the same item identifier in their ownedItems arrays.

That is to say, I want to execute:

db.Users.updateOne(
  { name: 'vic'}, 
  { $set: { 
      ownedItems: [
        'paperclip-1252', 
        'stapler-1337', 
        'notebook-42'
      ]
    } 
  })

... is only allowed to succeed if no other User document has an ownedItems array containing 'notebook-42'.

I could do a query to check, do any users besides 'vic' have any of the items in the proposed ownedItems array, like so:

db.Users.find({
  $and: [
    { 
      ownedItems: { 
        $in: ['paperclip-1252', 'stapler-1337','notebook-42'] 
      } 
    },
    {
      name: { $ne: 'vic' }
    } 
  ]  
})

... and only proceed with the update if that returns no results. However whose to say that in the time between I get that answer, and when I execute my update, the answer hasn't changed (e.g. because someone else inserted 'notebook-42' into their ownedItems)?

How can I atomically execute that query then update the document based on the result? While I have described this using MongoDB CLI syntax, I will, rather be implementing this logic in a NodeJS application using the mongodb package, in case that matters.



via vicatcu

No comments:

Post a Comment