MongoDB show duplicate key is null?

Question

MongoDB version 3.6

I got the duplicate key message when I executed this. What's the actual problem?

mongos> db.setup.findAndModify({
... query : {"_id" : ObjectId("5b3c7abbea95705803084cad")},
... update : {$set : { "test" : "xxxx" }},
... upsert : true
... })
2018-07-06T10:13:22.749+0000 E QUERY    [thread1] Error: findAndModifyFailed failed: {
    "ok" : 0,
    "errmsg" : "E11000 duplicate key error collection: testdb.setup index: name dup key: { : null }",
    "code" : 11000,
    "codeName" : "DuplicateKey",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1530872002, 603),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    },
    "operationTime" : Timestamp(1530872002, 602)
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCollection.prototype.findAndModify@src/mongo/shell/collection.js:724:1
@(shell):1:1
mongos>

score 7 · Accepted Answer · answered Jul 09 '18 at 02:38

This is the expected case if you have defined a unique index which is non-sparse: documents missing a value for the field with the unique index will have an indexed value of null. This means that only a single document in the collection can be missing the unique field.

The duplicate key message includes the index name and key violation:

"errmsg" : "E11000 duplicate key error collection: testdb.setup index: name dup key: { : null }",

In your example, the collection setup in database testdb has a unique index on the name field. The attempted upsert failed because the name field was missing and there was already a document in this collection with a missing or null value for name.

If you want to use a unique index without requiring the field to be present you have a few options:

Drop and recreate the unique index with the sparse property:

db.setup.createIndex(
    {name: 1},
    {unique:true, sparse:true}
)

Drop and recreate the unique index using a partial filter expression (which allows further criteria if needed):
```
db.setup.createIndex(
   { name: 1},
   { unique:true, partialFilterExpression: {name: {$exists:true }}}
)
```

Thanks, @Stennie, it's my bad to not see "name" field in error message. — tao hiko, Jul 10 '18 at 04:24
Hi. This is not working for me. It still complains about duplicates in the unique field. Can "duplicate documents" contains the field {...,"name":null,...} or they just should not contain that field? — Miguel, Feb 25 '19 at 11:39
@Miguel I suggest posting a new question with relevant details for your environment (MongoDB server version, driver version, error message, ...). — Stennie, Feb 25 '19 at 17:54
@Stennie, I just posted the question [here](https://dba.stackexchange.com/questions/230768/mongodb-does-not-allow-to-create-unique-partial-index-in-field-that-contains-nul). Thanks — Miguel, Feb 26 '19 at 14:39

score 2 · Answer 2 · answered Dec 17 '20 at 13:39

2

Delete the collection from the DB and then hit he API , it will work. There is no mistake in code.

answered Dec 17 '20 at 13:39

Zara Raja

21
1

it would be great if you'd register your account and add some details around your answer. Maybe a pointer to the documentation that shows how to delete the collection or something. – Hannah Vernon Dec 17 '20 at 15:05

score 1 · Answer 3 · answered Jul 06 '18 at 11:16

1

I found other indexes on that collection have been set "unique" to be "true" (exclude _id) then it shows error.

answered Jul 06 '18 at 11:16

tao hiko

73
1
1
5

MongoDB show duplicate key is null?

3 Answers3

Linked