Unique index in MongoDB

Question

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".

score 4 · Accepted Answer · edited May 23 '17 at 12:24

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.

But this fails if the order of elements under options change, "options": [ { "name" : "age", "value" : "25" }, { "name" : "id", "value" : "123" } ] won't give duplicate error. — Abhijeet Ahuja, Jun 27 '16 at 03:05
MongoDB embedded document indices work like embedded document queries, which mean they must match exactly and field orders matters. Add the fact on how multikey indices work, it seems that it is not possible to guarantee uniqueness alone with MongoDB itself if field order cannot be guaranteed. — kazenorin, Jun 27 '16 at 03:31

score 0 · Answer 2 · edited Jun 27 '16 at 05:29

0

yourDatabase.yourCollection.ensureIndex({"path": 1, "verb": 1, "switches": 1}, {"unique": true})

you can use ensureCollection() or createCollection as you like. https://docs.mongodb.com/manual/core/index-unique/

edited Jun 27 '16 at 05:29

DanielBarbarian

5,093
12
35
44

answered Jun 27 '16 at 05:21

Adil Blanco

616
2
6
23

Unique index in MongoDB

2 Answers2

Linked