array with unique values over all documents of one collection

Question

Situation: I have several documents of the same collection(account), each has an attribute of type array(string) named uniquestrings.

Problem: Each entry in uniquestrings must be unique over all documents in mongodb. It seems that MongoDB/Mongoose does not offer such a validation (neither addToSet¹ nor index: {unique: true}² solve the problem). Is there a pattern to restructure my document schema to make sure mongodb itself can validate it? At the moment the software itself checks it before updating the document.

E.g.

account {
  _id: 111,
  uniquestrings: ["a", "b", "c"]
}

account {
  _id: 222,
  uniquestrings: ["d", "e", "f"]
}

E.g. prevent account(222).uniquestrings.push("a"); by throwing a duplicate error from mongo.

¹ Uniqueness in an array is not enough
² Each item in array has to be unique across the collection

UPDATE1:

More examples. Affected Schema entry looks like:

var Account = new Schema({
    ...
    uniquestrings: [{type: String, index: {unique: true}}]
    ...
});

Now when create 4 account documents. I want only 1 and 2 be ok, and rest should fail.

var accountModel1 = new Account.Model({uniquestrings: ["a", "b"]});
accountModel1.save(); // OK
var accountModel2 = new Account.Model({uniquestrings: ["c", "d"]});
accountModel2.save(); // OK
var accountModel3 = new Account.Model({uniquestrings: ["c", "d"]});
accountModel3.save(); // FAIL => Not unique, so far so good
var accountModel4 = new Account.Model({uniquestrings: ["X", "d"]});
accountModel4.save(); // OK => But i Want this to faile because "d" is alreay in use.

I'm not sure to understand where the problem does occur... I just made some test and it works fine with unique index (using mongo shell). Could you post the problematic js code? — felix, Mar 27 '17 at 13:37

score 7 · Answer 1 · edited May 23 '17 at 11:54

According to this MongoDB Doc, There's no way to force MongoDB to enforce a unique index policy within a single document, but there is a way to enforce within separate documents.

db.collection.createIndex("a.b");

...will enforce uniqueness for these on a.b...

db.collection.insert({ a: [{b: 1}] });
db.collection.insert({ a: [{b: 1}] });

...but will not enforce uniqueness for this...

db.collection.insert({ a: [{b: 1},{b: 1}] ]);

...BUT if you strictly use $addToSet with the index...

db.collection.upsert({ $addToSet: { a: { b: 1 } } });

...and you compromise by not having an exception thrown, but rather the upsert quietly ignores the duplicate, which isn't what you want but closer.

So far, we've covered what's answered in another SO question, but keep reading and maybe you'll get what you're after.

Now, to achieve what your asking with native MongoDB requests is not possible out of the box, but you could ensureIndex and use a covered query to lookup the indexed array and throw an error if you find it, otherwise upsert as directed above.

So...

// Ensure index
db.collection.createIndex({ 'a.b': 1 });

// Test for existence and throws up if not unique
function insertUnique(newVal) {
  var exists = db.collection.find({'a.b': newVal});
  if (exists) {
    throw "Element is not unique in the collection: " + newVal;
  } else {
    db.collection.upsert({ $addToSet: { a: { b: 1 } } });
  }
}

// Use it later...
try {
  insertUnique(1);
  insertUnique(1); // it should barf
} catch (e) {
  console.warn(e);
}

Lastly, depending on which client you use, you may be able to extend the prototype (in JS) with the insertUnique method, and soon you'll forget you couldn't do this to begin with.

Good explanation. But the solution i was hoping for should not depend on "isunique" checks by myself. — MortalFool, Mar 31 '17 at 13:01
Sure... if you're willing to cede with the requirement for an array of uniquestrings, then you should create an index-constrained relational hashmap as proposed by @Vince Bowdren. :) — 4Z4T4R, Mar 31 '17 at 19:19

score 7 · Accepted Answer · answered Mar 29 '17 at 16:30

It might be possible, if you are willing to store the unique values in a different collection. It would be structured like this:

{ "uniquestring" : "a", "account" : 111 }
{ "uniquestring" : "b", "account" : 111 }
{ "uniquestring" : "c", "account" : 111 }
{ "uniquestring" : "d", "account" : 222 }
{ "uniquestring" : "e", "account" : 222 }
{ "uniquestring" : "f", "account" : 222 }

I am not an expert with Mongoose, but I believe that you can define Models to link collections together, with the account field here referencing the accounts collection's _id field.

Now, you can enforce the uniqueness with a straightforward index:

db.uniquestrings.createIndex( { "uniquestring" : 1 } , { unique : true } )

Now, your app will have a little more work to do when saving the data (it needs to save to the uniquestrings collection as well as the accounts collection), but you do now have database-level enforcement of the uniqueness of these strings, across the database.

PS edits are welcome from anybody with more detailed knowledge of how to implement and use such models in mongoose.

Interesting pattern. Ill try it out. – MortalFool Mar 31 '17 at 12:55 — MortalFool, Mar 31 '17 at 12:55

array with unique values over all documents of one collection

2 Answers2

Linked