Indexing array/subobject in mongoDB causes duplicate key error

Question

I have a collection where I will have a _children attribute like this:

{
  _children: {
    videoTags: [ { id: '1', name: 'one'}, { id: '2', name: 'two'} ],
  },
  a: 10
}

Since I WILL search in videoTags, I create an index as such:

> db.test4.createIndex({ "_children.videosTags.id" : 1 }, { "unique" : true } );
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

Trouble is, I can no longer add anything to that table since I get a duplicate index error. Here is how to reproduce it:

Step 1: insert to a collection

db.test4.insert({a:20}) WriteResult({ "nInserted" : 1 })

Step 2: make the index

db.test4.createIndex({ "_children.videosTags.id" : 1 }, { "unique" : true } ); { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 }

Step 3: try to insert again

db.test4.insert({a:30}) WriteResult({ "nInserted" : 0, "writeError" : { "code" : 11000, "errmsg" : "insertDocument :: caused by :: 11000 E11000 duplicate key error index: wonder_1.test4.$_children.videosTags.id_1 dup key: { : null }" } })

I think the issue here is that there is already a record where _children.videoTags.id is not defined.

However, what I expected was a behaviour where if videoTags.id was specified, it needed to be unique. Instead, an empty one is considered a "taken" key.

What am I doing that is stupidly wrong? This will work if you don't set unique as true, but I have the feeling I need to fix it for real...

This will be because another document already has an empty array ( or at least an element without an "id" field. You can either use "sparse", or ensure that all array elements have an "id" field. Placing a "unique" index that you know is going to be on an array element ( even if you really intend a unique id collection wide ) can be fraught with problems. It's generally not advised. — Blakes Seven
– Blakes Seven, Commented Mar 14, 2016 at 3:04

Saleem · Accepted Answer · 2016-03-24 21:19:23Z

1

There could be two reasons.

There could be other documents exists in collection with same _children.videosTags.id
It's quite possible that more than one document may have missing _children.videosTags.id" or having null value.

As you are creating unique key, null or empty values are give you tough time. Solution is either create sparse index and if your MongoDB version is 3.2+, create partial index. See documentation for partial indexes.

edited Mar 24, 2016 at 21:19

answered Mar 14, 2016 at 3:05

Saleem

9,0582 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Indexing array/subobject in mongoDB causes duplicate key error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related