Abhijeet Abhijeet - 5 months ago 20
JSON Question

Unique index in MongoDB

Can someone please help me with creation of unique index @ mongoDB for the following condition ?

Suppose I have a schema like this, and I want a unique compound index on 1.path 2.verb 3."switches.name" 4."switches.value"

{
"path": "somepath",
"verb": "GET",
"switches": [
{
"name": "id",
"value":"123"
},
{
"name": "age",
"value":"25"
}
]
}


So if I try to insert

{
"path": "somepath",
"verb": "GET",
"switches": [
{
"name": "id",
"value":"123"
},
{
"name": "age",
"value":"25"
}
]
}


I should get duplicate error, but if I insert

{
"path": "somepath",
"verb": "GET",
"switches": [
{
"name": "id",
"value":"123"
},
{
"name": "age",
"value":"24"
}
]
}


or

{
"path": "somepath",
"verb": "GET",
"switches": [
{
"name": "id",
"value":"123"
},
{
"name": "age",
"value":"25"
},
{
"name": "foo",
"value":"bar"
}
]
}


I shouldn't get any error.

Basically, I should be allowed to insert documents with distinct array "switches".

Answer

I am afraid what you want to achieve cannot be by your current schema.

First you have to understand how array on indexes work: https://docs.mongodb.com/manual/core/index-multikey/#multikey-indexes

To index a field that holds an array value, MongoDB creates an index key for each element in the array.

This suggests that arrays are not indexed as single objects, but are unwinded into multiple objects.

To achieve a similar result to what you want, you may consider using object property maps instead:

{
    "path" : "somepath", 
    "verb" : "GET", 
    "switches" : {
        "id": "123",
        "age": "25"
    }
}

And create as unique index as usual:

db.yourCollection.createIndex({"path": 1, "verb": 1, "switches": 1}, {"unique": true});

However, this is generally undesirable because querying for the key is non-trivial
(Also read about this here).

So alternatively, you can wrap the array within another object instead:

{
    "path" : "somepath", 
    "verb" : "GET", 
    "switches" : {
        "options": [
            {
                "name" : "id", 
                "value" : "123"
            }, 
            {
                "name" : "age", 
                "value" : "25"
            }
        ]
    }
}

With the same index:

db.yourCollection.createIndex({"path": 1, "verb": 1, "switches": 1}, {"unique": true});

If you plan to execute queries on the switches.options array, this would be the more desirable solution.