Unique Indexes and Schema Validation

Create a multi-property index

To enforce the application’s rules, create an index on the accounts.bank and accounts.number fields with the following characteristics:

• To ensure the bank and number fields do not repeat, make the index unique.

• To allow indexing of multiple fields, make the index compound.

• To allow indexing of documents inside an array, make the index of the type multikey.

You therefore create a compound multikey unique index with the following specification and options:

const specification = { "accounts.bank": 1, "accounts.number": 1 };
const options = { name: "Unique Account", unique: true };
db.users.createIndex(specification, options); // Unique Account

Create a partialFilterExpression

The index in its current state indexes all documents. However, this implementation can cause errors when you insert documents missing the accounts.bank or accounts.number fields.

For example, try to insert the following data into the users collection:

const user1 = { _id: 1, name: { first: "john", last: "smith" } };
const user2 = { _id: 2, name: { first: "john", last: "appleseed" } };
const account1 = { balance: 500, bank: "abc", number: "123" };
db.users.insertOne(user1);
db.users.insertOne(user2);

{ acknowledged: true, insertedId: 1 }
MongoServerError: E11000 duplicate key error collection: test.users index: Unique Account dup key: { accounts.bank: null, accounts.number: null }

When you try to insert a document that is missing one or more specified fields into an indexed collection, MongoDB:

• populates the missing fields into the inserted document

• sets their values to null

• adds an entry to the index

When you insert user1 without the accounts.bank and accounts.number fields, MongoDB sets them to null and adds a unique index entry. Any later insert that also lacks either field, such as user2, causes a duplicate key error.

To avoid this, use a partial filter expression so the index only includes documents that contain both fields. For more information, see Partial Index with Unique Constraint. Recreate the index using the following options:

const specification = { "accounts.bank": 1, "accounts.number": 1 };
const optionsV2 = {
   name: "Unique Account V2",
   partialFilterExpression: {
      "accounts.bank": { $exists: true },
      "accounts.number": { $exists: true }
   },
   unique: true
};
db.users.drop( {} ); // Delete previous documents and indexes definitions
db.users.createIndex(specification, optionsV2); // Unique Account V2

Test out your new index definition by inserting two users that do not contain the fields accounts.bank and accounts.number:

db.users.insertOne(user1);
db.users.insertOne(user2);

{ acknowledged: true, insertedId: 1 }
{ acknowledged: true, insertedId: 2 }

Test your database implementation

To ensure that you cannot register the same account for two different users, test the following code:

/* Cleaning the collection */
db.users.deleteMany( {} ); // Delete only documents, keep indexes definitions
db.users.insertMany( [user1, user2] );
/* Test */
db.users.updateOne( { _id: user1._id }, { $push: { accounts: account1 } } );
db.users.updateOne( { _id: user2._id }, { $push: { accounts: account1 } } );

{ acknowledged: true, insertedId: null, matchedCount: 1, modifiedCount: 1, upsertedCount: 0 }
MongoServerError: E11000 duplicate key error collection: test.users index: Unique Account V2 dup key: { accounts.bank: "abc", accounts.number: "123" }

The second updateOne command correctly returns an error, since you cannot add the same account for two separate users.

Test that the database does not allow you to add the same account multiple times for the same user:

/* Cleaning the collection */
db.users.deleteMany( {} ); // Delete only documents, keep indexes definitions
db.users.insertMany( [user1, user2] ); // Re-insert test documents
/* Test */
db.users.updateOne( { _id: user1._id }, { $push: { accounts: account1 } } );
db.users.updateOne( { _id: user1._id }, { $push: { accounts: account1 } } );
db.users.findOne( { _id: user1._id } );

{ acknowledged: true, insertedIds: { '0': 1, '1': 2 } }
{ acknowledged: true, insertedId: null, matchedCount: 1, modifiedCount: 1, upsertedCount: 0 }
{ acknowledged: true, insertedId: null, matchedCount: 1, modifiedCount: 1, upsertedCount: 0 }
_id: 1,
name: { first: 'john', last: 'smith' },
accounts: [
   { balance: 500, bank: 'abc', number: '123' },
   { balance: 500, bank: 'abc', number: '123' }
]

The returned code shows that the database incorrectly adds the same account multiple times to the same user. This error occurs because MongoDB indexes do not duplicate strictly equal entries with the same key values pointing to the same document.

When you insert account1 for the second time on the user, MongoDB does not create an index entry, so there are no duplicate values on it. To effectively implement your application design, your database should return an error if you attempt to add the same account multiple times to the same user.

Set up schema validation

To make your application reject adding the same account multiple times to the same user, implement Schema Validation. The following code uses the $expr operator to write an expression to verify if the items inside an array are unique:

const accountsSet = {
   $setIntersection: {
      $map: {
         input: "$accounts",
         in: { bank: "$$this.bank", number: "$$this.number" }
      }
   }
};
const uniqueAccounts = {
   $eq: [ { $size: "$accounts" }, { $size: accountsSet } ]
};
const accountsValidator = {
   $expr: {
      $cond: {
         if: { $isArray: "$accounts" },
         then: uniqueAccounts,
         else: true
      }
   }
};

When { $isArray: "$accounts" } is true, then the accounts array exists in a document, and MongoDB applies the uniqueAccounts validation logic. If the document passes the logic, it is valid.

The uniqueAccounts expression compares the size of the original accounts array to the size of accountsSet, which is created by the $setIntersection of a mapped version of accounts:

• The $map function transforms each entry in the accounts array to include only the accounts.bank and accounts.number fields.

• The $setIntersection function removes duplicates by treating the mapped array as a set.

• The $eq function compares the size of the original accounts array and the deduplicated accountsSet.

If both sizes are equal, all entries are unique by accounts.bank and accounts.number, then the validation returns true. If not, duplicates are present, and validation fails with an error.

You can test out your schema validation to ensure your database does not allow adding the same account multiple times to the same user:

/* Cleaning the collection */
db.users.drop( {} ); // Delete documents and indexes definitions
db.runCommand( {
   collMod: "users", // update collection to use schema validation
   validator: accountsValidator
} );
db.users.insertMany( [user1, user2] );
/* Test */
db.users.updateOne( { _id: user1._id }, { $push: { accounts: account1 } } );
db.users.updateOne( { _id: user1._id }, { $push: { accounts: account1 } } );

MongoServerError: Document failed validation
Additional information: {
   failingDocumentId: 1,
   details: {
      operatorName: '$expr',
      specifiedAs: {
      '$expr': {
         '$cond': {
            if: { '$and': '$accounts' },
            then: { '$eq': [ [Object], [Object] ] },
            else: true
         }
      }
      },
      reason: 'expression did not match',
      expressionResult: false
   }
}

The second updateOne() command returns a Document failed validation error, indicating that the database now rejects any attempt to add the same account multiple times to the same user.