Unique Indexes and Documents in an Array of Documents
We are building an app that summarizes a user’s finances. The main screen shows who the user is and the balances of every bank account connected to our app. Following MongoDB’s common guidance, “Data that is accessed together should be stored together.” With that in mind, we sketched the following document model for the main page data:
db.orders.find({ customer_id: "12345", status: "pending" });
From the app’s behavior, we set these rules:
- A user may sign up without linking any bank accounts.
- An account is identified by the fields bank and number.
- No single bank account should appear under two different users.
- No single bank account should appear more than once for the same user.
To enforce this, we chose an index with these properties:
- Because bank and number must be unique, the index should be Unique
- Since more than one field is indexed, it is a Compound
- Because those fields live inside an array of subdocuments, it becomes Multikey
Putting it together, we end up with a Compound Multikey Unique Index, defined like this:
const specification = { "accounts.bank": 1, "accounts.number": 1 };
const options = { name: "Unique Account", unique: true };
To verify the behavior, we prepared the following test data:
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" };
First, we insert the users:
db.users.createIndex(specification, options); // Unique Account
db.users.insertOne(user1); // { acknowledged: true, insertedId: 1)}
db.users.insertOne(user2); // MongoServerError: E11000 duplicate key error collection: test.users index: Unique Account dup key: { accounts.bank: null, accounts.number: null }
So far so good, except we hit an error before touching the accounts. What happened?
The error says our Unique Account index found a duplicate key with value null for accounts.bank and accounts.number. This comes from how MongoDB indexing works. When a document is written to an indexed collection and it lacks one or more indexed fields, those missing fields are treated as null, and an index entry is created for null. In our test, inserting user1 created an index entry where both accounts.bank and accounts.number were null. Inserting user2 tried to create the same null entry again. The unique constraint blocked it. For more on missing fields with unique indexes, see the docs.
The fix is to index only documents that actually contain accounts.bank and accounts.number. We can do this by adding a partial filter expression to the index options. Now we have a Compound Multikey Unique Partial Index, which sounds fancy, but here is the setup:
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,
};
Back to the tests:
// Cleaning our environment
db.users.drop({}); // Delete documents and indexes definitions
/* Tests */
db.users.createIndex(specification, optionsV2); // Unique Account V2
db.users.insertOne(user1); // { acknowledged: true, insertedId: 1)}
db.users.insertOne(user2); // { acknowledged: true, insertedId: 2)}
Great. With the new index, inserting two users without accounts works. Next, we check for the same account across two different users:
// 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 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}
db.users.updateOne({ _id: user2._id }, { $push: { accounts: account1 } }); // MongoServerError: E11000 duplicate key error collection: test.users index: Unique Account V2 dup key: { accounts.bank: "abc", accounts.number: "123" }
As expected, that insert fails. Now we try duplicating the same account for the same user:
// 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 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}
db.users.findOne({ _id: user1._id }); /*{
_id: 1,
name: { first: 'john', last: 'smith' },
accounts: [
{ balance: 500, bank: 'abc', number: '123' },
{ balance: 500, bank: 'abc', number: '123' }
]
}*/
Surprise, that one does not fail. This is another detail of MongoDB indexing. The documentation on unique constraints explains that MongoDB indexes do not store duplicate key entries that point to the same document. When we inserted account1 for the same user a second time, no new index entry was created. The index still had no duplicate values, so no error occurred.
Some readers might suggest using $addToSet instead of $push to avoid inserting duplicates. Not in this case. $addToSet considers the entire subdocument, while our definition of uniqueness is based only on bank and number.
What now? We packed our index with options, yet the app still allows a situation we do not want.
One straightforward workaround is to adjust the update filter so it only matches the user document if the target account is not already present in accounts.
// Cleaning the collection
db.users.deleteMany({}); // Delete only documents, keep indexes definitions
db.users.insertMany([user1, user2]);
/* Test */
const bankFilter = {
$not: { $elemMatch: { bank: account1.bank, number: account1.number } }
};
db.users.updateOne(
{ _id: user1._id, accounts: bankFilter },
{ $push: { accounts: account1 } }
); // { ... matchedCount: 1, modifiedCount: 1 ...}
db.users.updateOne(
{ _id: user1._id, accounts: bankFilter },
{ $push: { accounts: account1 } }
); // { ... matchedCount: 0, modifiedCount: 0 ...}
db.users.findOne({ _id: user1._id }); /*{
_id: 1,
name: { first: 'john', last: 'smith' },
accounts: [ { balance: 500, bank: 'abc', number: '123' } ]
}*/
This prevents the duplicate from being added for the same user, and it does not throw an error.
That behavior is not ideal. We want the operation to fail loudly so the user understands it is not allowed, and so future changes do not silently bypass the rule. People come and go, tribal knowledge fades, shortcuts creep in. We want a guardrail that stops even the boldest person from changing production data directly.
MongoDB schema validation to the rescue.
Quick reminder before diving in. MongoDB best practices recommend enforcing validation in the application, with MongoDB’s schema validation as a backstop.
Schema validation allows $expr, which lets us write an aggregation expression that MongoDB evaluates on insert and update. This means we can check that items inside an array are unique.
After some iterations, we arrived at this expression:
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,
},
},
};
It looks dense at first glance, so let’s unpack it.
Inside $expr we use a $cond. If the condition in if is true, MongoDB evaluates then. Otherwise it evaluates else. In our case, if { $isArray: “$accounts” } is true, we run the uniqueAccounts logic. If the array is missing, we return true so the document passes validation.
Within uniqueAccounts, we compare the
$size of two arrays with $eq. The first is the size of $accounts. The second is the size of accountsSet, which comes from
$setIntersection. $setIntersection removes duplicates from a set. The array we pass into $setIntersection is built by $map so that each account is reduced to only bank and number. If both sizes match, validation passes. If they differ, validation fails and the write errors out.
Let’s see it in action:
// Cleaning the collection
db.users.drop({}); // Delete documents and indexes definitions
db.createCollection("users", { validator: accountsValidator });
db.users.createIndex(specification, optionsV2);
db.users.insertMany([user1, user2]);
/* Test */
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}
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
}
}*/
Success. Our data now resists direct or indirect attempts to add duplicate accounts.
To reach this outcome, we reviewed unique indexes, used a filter in the update operation to avoid silent duplicates, and added MongoDB schema validation with $expr to provide a stronger safety net for our data.