> This page location: /docs.md > /docs/development.md > /docs/manual/indexes.md > /docs/manual/applications/indexes.md

> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt

# Unique Indexes and Schema Validation

To ensure your database adheres to your application design, you can strategically create indexes to combine index properties with schema validation.

## About this Task

Consider an application that summarizes a user’s finances. The main page of the application displays the user’s ID and the balances on all of their banking accounts synced with the application.

The application stores its user information in a collection called `users`. The `users` collection contains documents with the following schema:

```javascript
db.users.insertOne( {
   _id: 1,
   name: { first: "john", last: "smith" },
   accounts: [
      { balance: 500, bank: "abc", number: "123" },
      { balance: 2500, bank: "universal bank", number: "9029481" }
   ]
} )
```

The application requires the following rules:

- A user can register in the application and not sync a bank account.

- A user identifies an account by its `bank` and `number` fields.

- A user cannot register the same account for two different users.

- A user cannot register the same account multiple times for the same user.

To design your database so that it confines its documents to the application’s rules, combine a unique index and schema validation on your database using the following procedure.

## Steps

1. 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.](https://www.mongodb.com/docs/manual/core/index-unique.md#std-label-index-type-unique)

   - To allow indexing of multiple fields, make the index [compound.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound)

   - To allow indexing of documents inside an array, make the index of the type [multikey.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey)

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

   ```javascript
   const specification = { "accounts.bank": 1, "accounts.number": 1 };
   const options = { name: "Unique Account", unique: true };

   db.users.createIndex(specification, options); // Unique Account
   ```

2. 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:

   ```javascript
   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);
   ```

   **Output:**

   ```json
   { 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](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-partial-index-with-unique-constraints). Recreate the index using the following options:

   ```javascript
   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`:

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

   **Output:**

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

3. Test your database implementation

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

   ```javascript
   /* 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 } } );
   ```

   **Output:**

   ```json
   { 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:

   ```javascript
   /* 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 } );
   ```

   **Output:**

   ```json
   { 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.

4. Set up schema validation

   To make your application reject adding the same account multiple times to the same user, implement [Schema Validation](https://www.mongodb.com/docs/manual/core/schema-validation.md#std-label-schema-validation-document). The following code uses the [`$expr`](https://www.mongodb.com/docs/manual/reference/operator/query/expr.md#mongodb-query-op.-expr) operator to write an expression to verify if the items inside an array are unique:

   ```javascript
   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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/setintersection.md#mongodb-expression-exp.-setIntersection) of a mapped version of `accounts`:

   - The [`$map`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/map.md#mongodb-expression-exp.-map) function transforms each entry in the `accounts` array to include only the `accounts.bank` and `accounts.number` fields.

   - The [`$setIntersection`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/setintersection.md#mongodb-expression-exp.-setIntersection) function removes duplicates by treating the mapped array as a set.

   - The [`$eq`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/eq.md#mongodb-expression-exp.-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:

   ```javascript
   /* 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 } } );
   ```

   **Output:**

   ```json
   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.
