#StackBounty: #mongodb How do I set multiple unique indexes in schema in mongodb?

Bounty: 250

I have a collection that keeps getting duplicate documents because I have multiple servers writing to the database and sometimes two servers will insert the same document because they update at (almost) the same time. The problem is, there are different ways that documents can be considered unique, and not all fields are required all the time. Here are some examples.

This is my schema:

const Collection = new Schema(
    {
        accountID: {type: Schema.Types.ObjectId, ref: "Account", required: true},
        startDate: Date,
        threadID: String,
        confirmationCode: String,
        externalID: String,
    },
    {timestamps: true}
);

A document is unique if:

  1. accountID,startDate, and threadID are all the same as another document
  2. accountID, and confirmationCode are both the same as another document
  3. accountID, and externalID are both the same as another document

Here are some indexes but I’m not sure they will work.

Reservation.index(
    {
        accountID: 1,
        startDate: 1,
        threadID: 1
    },
    {name: "Unique_index_1", unique: true, sparse: true}
);

Reservation.index(
    {
        accountID: 1,
        confirmationCode: 1
    },
    {name: "Unique_index_2", unique: true, sparse: true}
);

Reservation.index(
    {
        accountID: 1,
        externalID: 1
    },
    {name: "Unique_index_3", unique: true, sparse: true}
);

Here are some example of documents and some duplicates:

// Unique
{
    _id: "id_1",
    accountID: "account1", 
    startDate: "2021-04-01",
    threadID: "threadID_1",
    confirmationCode: "confirmationCode_1" 
    // externalID (not set)
}
{
    _id: "id_2",
    accountID: "account1", 
    startDate: "2021-04-02",
    threadID: "threadID_1",
    confirmationCode: "confirmationCode_2" 
    // externalID (not set)
}
{
    _id: "id_3",
    accountID: "account1",
    startDate: "2021-04-03", 
    threadID: "threadID_1",
    // confirmationCode (not set)
    // externalID (not set)
}
{
    _id: "id_4",
    accountID: "account1",
    // startDate (not set)
    // threadID (not set)
    confirmationCode: "confirmationCode_3" 
    // externalID (not set)
}
{
    _id: "id_5",
    accountID: "account2",
    startDate: "2021-04-01", 
    // threadID: (not set),
    // confirmationCode (not set)
    externalID: "externalID_1"
}
// Duplicates of the docs above
{
    accountID: "account1",
    // startDate (not set)
    // threadID (not set)
    confirmationCode: "confirmationCode_1" // <-- is same as doc id_1 (violates Unique_index_2)
    // externalID (not set)
}
{
    accountID: "account1",
    startDate: "2021-04-01",  // <-- is same as doc id_1 (violates Unique_index_1)
    threadID: "threadID_1", // <-- is same as doc id_1 (violates Unique_index_1)
    // confirmationCode (not set)
    // externalID (not set)
}
{
    accountID: "account1",
    startDate: "2021-04-02",  // <-- is same as doc id_2 (violates Unique_index_1)
    threadID: "threadID_1", // <-- is same as doc id_2 (violates Unique_index_1)
    // confirmationCode (not set)
    // externalID (not set)
}
{
    accountID: "account2",
    // startDate (not set)
    // threadID (not set)
    // confirmationCode (not set)
    externalID: "externalID_1" // <-- is same as doc id_5 (violates Unique_index_3)
}

Uniqueness should be checked in this order:

  1. If accountID and confirmationCode exists and are unique go to #2
  2. If accountID and externalID exists and are unique go to #3
  3. If accountID, startDate and threadID exists and are unique insert new document.

If it finds a match to any of the above, it should not insert a doc but rather update. I think my use of sparse is correct but I want to make sure.

Will my unique index work for what I want?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.