Unique Indexes Quirks and Unique Documents in an Array of Documents

1
const user = {
2
    _id: 1,
3
    name: { first: "john", last: "smith" },
4
    accounts: [
5
        { balance: 500, bank: "abc", number: "123" },
6
        { balance: 2500, bank: "universal bank", number: "9029481" },
7
    ],
8
};

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

1
const user1 = { _id: 1, name: { first: "john", last: "smith" } };
2
const user2 = { _id: 2, name: { first: "john", last: "appleseed" } };
3
const account1 = { balance: 500, bank: "abc", number: "123" };

1
db.users.createIndex(specification, options); // Unique Account
2
3
db.users.insertOne(user1); // { acknowledged: true, insertedId: 1)}
4
db.users.insertOne(user2); // MongoServerError: E11000 duplicate key error collection: test.users index: Unique Account dup key: { accounts.bank: null, accounts.number: null }

1
const specification = { "accounts.bank": 1, "accounts.number": 1 };
2
const optionsV2 = {
3
 name: "Unique Account V2",
4
 partialFilterExpression: {
5
   "accounts.bank": { $exists: true },
6
   "accounts.number": { $exists: true },
7
 },
8
 unique: true,
9
};

1
// Cleaning our environment
2
db.users.drop({}); // Delete documents and indexes definitions
3
4
/* Tests */
5
db.users.createIndex(specification, optionsV2); // Unique Account V2
6
db.users.insertOne(user1); // { acknowledged: true, insertedId: 1)}
7
db.users.insertOne(user2); // { acknowledged: true, insertedId: 2)}

1
// Cleaning the collection
2
db.users.deleteMany({}); // Delete only documents, keep indexes definitions
3
db.users.insertMany([user1, user2]);
4
5
/* Test */
6
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}
7
8
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" }

1
// Cleaning the collection
2
db.users.deleteMany({}); // Delete only documents, keep indexes definitions
3
db.users.insertMany([user1, user2]);
4
5
/* Test */
6
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}
7
8
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}
9
10
db.users.findOne({ _id: user1._id }); /*{
11
 _id: 1,
12
 name: { first: 'john', last: 'smith' },
13
 accounts: [
14
   { balance: 500, bank: 'abc', number: '123' },
15
   { balance: 500, bank: 'abc', number: '123' }
16
 ]
17
}*/

1
// Cleaning the collection
2
db.users.deleteMany({}); // Delete only documents, keep indexes definitions
3
db.users.insertMany([user1, user2]);
4
5
/* Test */
6
const bankFilter = { 
7
    $not: { $elemMatch: { bank: account1.bank, number: account1.number } } 
8
};
9
10
db.users.updateOne(
11
    { _id: user1._id, accounts: bankFilter },
12
    { $push: { accounts: account1 } }
13
); // { ... matchedCount: 1, modifiedCount: 1 ...}
14
15
db.users.updateOne(
16
    { _id: user1._id, accounts: bankFilter },
17
    { $push: { accounts: account1 } }
18
); // { ... matchedCount: 0, modifiedCount: 0 ...}
19
20
db.users.findOne({ _id: user1._id }); /*{
21
 _id: 1,
22
 name: { first: 'john', last: 'smith' },
23
 accounts: [ { balance: 500, bank: 'abc', number: '123' } ]
24
}*/

1
const accountsSet = { 
2
    $setIntersection: { 
3
        $map: { 
4
            input: "$accounts", 
5
            in: { bank: "$$this.bank", number: "$$this.number" } 
6
        },
7
    },
8
};
9
10
11
const uniqueAccounts = {
12
    $eq: [{ $size: "$accounts" }, { $size: accountsSet }],
13
};
14
15
16
const accountsValidator = {
17
    $expr: {
18
        $cond: {
19
            if: { $isArray: "$accounts" },
20
            then: uniqueAccounts,
21
            else: true,
22
        },
23
    },
24
};

1
// Cleaning the collection
2
db.users.drop({}); // Delete documents and indexes definitions
3
db.createCollection("users", { validator: accountsValidator });
4
db.users.createIndex(specification, optionsV2);
5
db.users.insertMany([user1, user2]);
6
7
/* Test */
8
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}
9
10
db.users.updateOne(
11
    { _id: user1._id },
12
    { $push: { accounts: account1 } }
13
); /* MongoServerError: Document failed validation
14
Additional information: {
15
 failingDocumentId: 1,
16
 details: {
17
   operatorName: '$expr',
18
   specifiedAs: {
19
     '$expr': {
20
       '$cond': {
21
         if: { '$and': '$accounts' },
22
         then: { '$eq': [ [Object], [Object] ] },
23
         else: true
24
       }
25
     }
26
   },
27
   reason: 'expression did not match',
28
   expressionResult: false
29
 }
30
}*/