MongoDB Unique Index on nested objects

Question

This is my MongoDB collection :

{
    "_id": "0",
    "profiles": {
        "A123": {
            "name": "Mike"
        },
        "B456": {
            "name": "John"
        }
}

I would like to make sure that fields "name" can not have the same value. If someone is named Mike, nobody can be called Mike

How can I make a index that would throw an error if I try to create a new object inside this collection in "profiles" with a name that is already in use ?

why u didnt make a profile Collection and "name" as a attribute that would be more convincing. After that you can just query the database using Collection.findOne({name:name}) and do relevant operations if it is already present or not. — vinod
– vinod, Commented Sep 13, 2020 at 7:36

Tom Slabbaert · Accepted Answer · 2020-09-13 07:24:00Z

2

With your current structure you can't. A unique index this way where you can use a certain nested value as the reference as the point of a unique index is to differentiate between documents and not within a single one.

You can do either of the two following things:

Change document structure. change your document to something like:

[
  {
    "_id": "0",
    "profile": {
      "name": "Mike",
      "key": "A123"
    }
  },
  {
    "_id": "1",
    "profile": {
      "name": "John",
      "key": "B456"
    }
  }
]

With a structure like this you can create a unique index on profile.name like so:

db.collection.createIndex({"profile.name" : 1}, { unique: true } );

Have another collection called names and use that as your reference. Mind you this can cause a lot of overhead operations if you update the names often. if not this would be quite a quick and easy solution to your problem as you will only have to update it on inserts / deletion operations.

answered Sep 13, 2020 at 7:24

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

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

1 Comment

Arty Over a year ago

Thanks, I think this is the most relevant thing to do as i'm starting with MongoDB architecture.

Mallik · Accepted Answer · 2020-09-13 07:59:00Z

//code from output of windows mongo shell client CLI(command line interface)
//create a collection for profiles with objects shown
db.test5.insertMany(
[
 {
    "_id": "0",
    "profiles": {
        "A123": {
            "name": "Mike"
        },
        "B456": {
            "name": "John"
            }
   }
   }
]);
> db.test5.find().pretty();
{
        "_id" : "0",
        "profiles" : {
                "A123" : {
                        "name" : "Mike"
                },
                "B456" : {
                        "name" : "John"
                }
        }
}
>
> db.test5.find().pretty();
{
        "_id" : "0",
        "profiles" : {
                "A123" : {
                        "name" : "Mike"
                },
                "B456" : {
                        "name" : "John"
                }
        }
}
>//create index with the path leading to name, 
//"*" is used as its not unique within the object path
//check index is created
> db.test5.getIndexes();
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "test.test5"
        },
        {
                "v" : 2,
                "key" : {
                        "profiles.*.name" : 1
                },
                "name" : "profiles.*.name_1",
                "ns" : "test.test5"
        }
]
//test duplicate document insertion for "Mike" as document exists for "Mike"
> db.test5.insertOne(
...  {
...     "_id": "0",
...     "profiles": {
...         "A123": {
...             "name": "Mike"
...         }
...      }
...    }
... );
2020-09-13T13:23:04.908+0530 E  QUERY    [js] WriteError({
        "index" : 0,
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error collection: test.test5 index: _id_ dup key: { _id: \"0\" }",
        "op" : {
                "_id" : "0",
                "profiles" : {
                        "A123" : {
                                "name" : "Mike"
                        }
                }
        }
}) :
WriteError({
        "index" : 0,
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error collection: test.test5 index: _id_ dup key: { _id: \"0\" }",
        "op" : {
                "_id" : "0",
                "profiles" : {
                        "A123" : {
                                "name" : "Mike"
                        }
                }
        }
})
WriteError@src/mongo/shell/bulk_api.js:458:48
mergeBatchResults@src/mongo/shell/bulk_api.js:855:49
executeBatch@src/mongo/shell/bulk_api.js:919:13
Bulk/this.execute@src/mongo/shell/bulk_api.js:1163:21
DBCollection.prototype.insertOne@src/mongo/shell/crud_api.js:264:9
@(shell):1:1
//re-check the collection, no duplicates are inserted
> db.test5.find().pretty();
{
        "_id" : "0",
        "profiles" : {
                "A123" : {
                        "name" : "Mike"
                },
                "B456" : {
                        "name" : "John"
                }
        }
}
>

Error looks like this failed because _id was duplicate, not the name field.

Collectives™ on Stack Overflow

MongoDB Unique Index on nested objects

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related