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

What is the question? You already answered yourself, use unique index with sparse... Also why do you index deleted? — libik
That does not work, thats why the question is there. I index deleted, because it is data which needs to be kept even if the user deletes it. So deleted is either null or the date when the user deleted the data. — Andi Giga
Actually I don't save ´null´ on deleted just a date if it was deleted, that was wrong. — Andi Giga

libik libik · Accepted Answer · 2016-03-10T16:33:24

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.