MongoDB Enforcing Complex constraints across documents in a collection

Question

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' (or 'paperclip-1252', or 'stapler-1337').

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.

Answer 1

Unique indexes are the only way I can think of that cross document constraints can be applied inside Mongodb, which happens to work in this case.

Create a unique index on ownedItems. Mongo will index on each set value rather than the set itself.

To allow duplicate empty arrays (ab)use the sort order of null and empty records going before populated arrays.

db.Users.createIndex({ ownedItems: 1 }, { 
  unique:1, 
  partialFilterExpression: { 
    ownedItems: { $gt: []  }
  }
})

Testing

db.Users.insert({name: 'vic', ownedItems: ['one','two']}) // Works

db.Users.insert({name: 'ted', ownedItems: ['one']}) // E11000 duplicate key
db.Users.insert({name: 'ted', ownedItems: ['two']}) // E11000 duplicate key
db.Users.insert({name: 'ted', ownedItems: ['one', 'three']}) // E11000 duplicate key

db.Users.insert({name: 'ted', ownedItems: ['three']}) // Works

db.Users.insert({name: 'ann', ownedItems: ['four','four']})  // E11000 duplicate key

db.Users.insert({name: 'jim', ownedItems: []}) 
db.Users.insert({name: 'pen', ownedItems: []}) // Works

If you need anything more complex you'll need to implement the logic in your app, including the transaction system if needed. Traditional RDBMS like Maria/Postgres have better support for more complex DB logic and transactions.