Will unique indexes ignore fields that don't exist?

Question

I have a MongoDB index:

Reservation.index(
    {
        source: 1,
        accountID: 1, // <-- This is the only required field
        confirmationCode_1: 1,
        confirmationCode_2: 1,
        confirmationCode_3: 1
    },
    {name: "Unique_reservation_index_1", unique: true}
);

Here are some sample entries I have in the database and I want to make sure that duplicates can't be made:

[
    {
        source: "A",
        accountID: "AAA",
        confirmationCode_1: "ABC"
    },
    {
        source: "B",
        accountID: "BBB",
        confirmationCode_1: "ABC"
        confirmationCode_2: "DEF"
    },
    {
        source: "C",
        accountID: "CCC",
        confirmationCode_3: "GHI"
    }
]

Sometimes I have confirmationCode_1 set and not confirmationCode_2 other times I both confirmationCode_1 and confirmationCode_2 set. Other times I have confirmationCode_3 set.

I want MongoDB to allow me to have the following doc (missing the confirmationCode_2 and confirmationCode_3 fields). Will it let me with the above index?

    {
        source: "A",
        accountID: "123",
        confirmationCode_1: "ABC"
    }

Will it prevent me from adding two similar docs with confirmationCode_2 not defined or will that be considered the same? For example, if it does allow the above doc, will this be prevented?

    {
        source: "A",
        accountID: "AAA",
        confirmationCode_1: "ABC_2"
    }

If I don't supply the confirmationCode_2 field, does it set the confirmationCode_2 field to null?

If I change the unique index to include sparse: true, how will it act differently?

Reservation.index(
    {
        source: 1,
        accountID: 1, // <-- This is the only required field
        confirmationCode_1: 1,
        confirmationCode_2: 1
    },
    {name: "Unique_reservation_index_1", unique: true, sparse: true}
);

user_14732669 · Answer

From MongoDB document on unique Index,

A unique index ensures that the indexed fields do not store duplicate values

undefined / empty / null field is allowed as long as you do not have the same tuple of values of the fields in the compound index.

Below is my actual testing result:

You can observe that the document is successfully added under the unique index.

user_4980141 · Answer

Will unique indexes ignore fields that don't exist?

No, the index will store a null value for this field, MongoDB will enforce uniqueness on the combination of the index key values.

//You have this docuemt on you MongoDB
{
    source: "A",
    accountID: "123",
    confirmationCode_1: "ABC"
}

//You try to insert the next document, note the missing "accountID" field
//Even though "source" and "confirmationCode_1"
//This operation SUCCESS because
//MongoDB will enforce uniqueness on the "combination" of the index key values
{
    source: "A",
    confirmationCode_1: "ABC"
}


//You try to insert the next document
//The operation FAIL to insert the document
//because of the violation of the unique constraint 
//on the combination of key values
{
    source: "A",
    accountID: "123",
    confirmationCode_1: "ABC"
}

What if you change unique: true to unique: true, sparse: true ?

An index that is both sparse and unique prevents collection from having documents with duplicate values for a field but allows multiple documents that omit the key.

Will unique indexes ignore fields that don't exist?

2 Answers