How to add a unique index on an embedded array in Mongo DB collection

Question

I am trying to create unique index on scorecardList.filename field in below mentioned mongo collection. The purpose is we should not be able to create another element in scorecardList with same filename.

Mongo Schema:

{
    "Name": "Ravikant Khond",
    "PIN" : "411057",
    "scorecardList": [
        {
            "fileName" : "ScoreCard_April_2016.pdf",
            "runDate" : ISODate("2016-05-01T00:00:00.000Z"),
            "month" : "April",
            "year" : "2016"
        },
        {
            "fileName" : "ScoreCard_May_2016.pdf",
            "runDate" : ISODate("2016-06-01T00:00:00.000Z"),
            "month" : "May",
            "year" : "2016"
        }
    ]
}

[1]

Mongo command I tried to use while creating unique index is as follows:

db.testing.createIndex(
    { "scorecardList.filename": 1 },
    { 
        unique: true, 
        partialFilterExpression: { 
            "scorecardList.filename": { $exists: true } 
        } 
    }
);

Even though the index has been created I am able to add a scorecard with existing filename.

Please help.

Answer 1

you cannot enforce complete uniqueness when it comes to arrays

As far as I know, unique indexes only enforce uniqueness across different documents, so this would throw a duplicate key error:

db.food.insert( { id: 123, name:bread, ingredients: [ { id: 456 } ] } )
db.food.insert( { id: 123, name:bread, ingredients: [ { id: 456 } ] } )

But this is allowed:

db.food.insert( { id: 123, name:bread, ingredients: [ { id: 456 }, { id: 456 } ] } )

I'm not sure if there's any way enforce the constraint you need at the Mongo level, maybe it's something you could check in the application logic when you insert of update?

alternatively, using the $addToSet function would check if the value already exists before adding it.