Mongoose Compound Index Unique + Sparse

Question

I want to create an index which ensures, that I don't have a duplicate serialNr within the combination of a manufacturer + art. But some items don't have a serialNr. These I don't want to check/index.

Code

mySchema.index({ serialNr: 1, art: 1 , manufacturer: 1, deleted: 1}, { unique: true, sparse: true)

I tried it also with adding a partial Filter: partialFilterExpression: { serialNr: {$ne:null} } to the index options.

Question

How can I index it that inserting: [{art: 'a', manufacturer:'a', deleted:null}, {art: 'a', manufacturer:'a', deleted:null}] passes

and

[{serialNr: '123', art: 'a', manufacturer:'a', deleted:null}, {serialNr: '123', art: 'a', manufacturer:'a', deleted:null}]fails

I'm not trying to save: [{serialNr: null, art: 'a', manufacturer:'a', deleted:null}, {serialNr: null, art: 'a', manufacturer:'a', deleted:null}]

---

The mongo docs state, that it should not be indexed if a field is missing in a compound index:

Sparse indexes selects documents to index solely based on the existence of the indexed field, or for compound indexes, the existence of the indexed fields. https://docs.mongodb.org/manual/core/index-partial/

EDIT:

New Index

implantSchema.index({ serialNr: 1, art: 1 , manufacturer: 1, deleted: 1}, { unique: true, partialFilterExpression: { serialNr: {$exists:true} }})

Before I make changes on the index I drop my collection in my dev-database, to ensure that there is no old index existing. This is the new index which is returned on db.items.getIndexes()

/* 0 */
{
    "0": {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "LOC_db.items"
    },
    "1": {
        "v" : 1,
        "unique" : true,
        "key" : {
            "serialNr" : 1,
            "art" : 1,
            "manufacturer" : 1,
            "deleted" : 1
        },
        "name" : "serialNr_1_art_1_manufacturer_1_deleted_1",
        "ns" : "LOC_db.items",
        "partialFilterExpression" : {
            "serialNr" : {
                "$exists" : true
            }
        },
        "background" : true
    }
}

Data

• I have a pre save hook where I check the value of serialNr, which does not exist before, the dup error is returned.
• When querying the database there is no doc existing which has null, "" as value for the serialNr.
• The query db.items.find({ serialNr: {$exists:true} }) does not match any items.

Error

insertDocument :: caused by :: 11000 E11000 duplicate key error index: LOC_db.items.$serialNr_1_art_1_manufacturer_1_deleted_1 dup key: { : null, : "Robo", : "Olymp", : null }

Additional Information

Maybe the error is related to: https://github.com/Automattic/mongoose/issues/2457

Answer 1

You were actually quite close to the solution, all you need to use this when creating index :

partialFilterExpression: { serialNr: {$exists:true} }

---

Be sure to have at least 3.2 version of mongo

---

Example :

db.col.createIndex({a:1, b:1},{unique:true, partialFilterExpression: { a: {$exists:true} })
db.col.save({a:50, b:27}) //pass
db.col.save({a:50, b:27}) //fails
db.col.save({b:27}) //pass
db.col.save({b:27}) //pass
db.col.save({b:27}) //pass
db.col.save({a:50, b:27}) //fails
db.col.save({a:50}) //pass
db.col.save({a:50}) //fails

---

PS : Maybe you can do little "hack" and use this : partialFilterExpression: { serialNr: {$exists:true}, serialNr: {$gte:0} }

I tried using $ne, but it was throwing me an error.