How to define a unique index for an element of an arrary in Mongo

Question

I'd like to define a unique index for the last element of an array? is that even possible in mongo?

only status[this.status.length-1].state and user_id for specific status.state (e.g active) has to be unique.

note: the last element of status array define the user's current status.

e.g: 2 user_id with status[this.status.length-1].state: **revoked** state can be exist. but 2 user_id with status[this.status.length-1].state: **active** cannot be exist.

{
    "_id" : ObjectId("59a609778c6929e7122322cf"),
    "user_id": '9e2cZ'
    "status" : [ 
        {
            "state" : "active",
            "updatedAt" : ISODate("2017-08-30T21:23:21.158Z"),
            "createdAt" : ISODate("2017-08-30T21:23:21.158Z")
        }, 
        {
            "updatedAt" : ISODate("2017-08-31T22:24:02.613Z"),
            "createdAt" : ISODate("2017-08-31T22:24:02.613Z"),
            "state" : "revoked"
        }, 
        {
            "updatedAt" : ISODate("2017-08-31T22:25:02.888Z"),
            "createdAt" : ISODate("2017-08-31T22:25:02.888Z"),
            "state" : "active"
        }
    ],

}

Answer 1

Index an array element

The only way that I know to perform it is to reverse your problem. The latest status should be in first position of your array. You can insert it with unshift function. And create your index this way :

db.coll.createIndex({user_id:1, 'status.0.state': 1})

This will index the first element of your array.

Here is the explain that show the use of the index :

db.coll.find({user_id: 1, 'status.0.state': "active"}).explain().queryPlanner.winningPlan
{
        "stage" : "FETCH",
        "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                        "user_id" : 1,
                        "status.0.state" : 1
                },
                "indexName" : "user_id_1_status.0.state_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                        "user_id" : [ ],
                        "status.0.state" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                        "user_id" : [
                                "[1.0, 1.0]"
                        ],
                        "status.0.state" : [
                                "[\"active\", \"active\"]"
                        ]
                }
        }
}

To insert your status in node you should turn

user.status.push(newStatus);

into

user.status.unshift(newStatus);

Unique index with partial indexes

To have a uniqueness on the active status, you can combine the previous answer with a partial index. The partial index will index only the active users.

Here is the index creation command :

db.users.createIndex(
  {user_id:1, 'status.0.state': 1}, 
  {unique: true, partialFilterExpression: {"status.0.state": {$eq: 'active'}}}
)

Then, I insert 2 users, one active and one inactive

> db.users.find().pretty()
{
    "_id" : ObjectId("59a609778c6929e7122322cd"),
    "user_id" : "9e2cZ",
    "status" : [
        {
            "state" : "inactive",
            "updatedAt" : ISODate("2017-08-30T21:23:21.158Z"),
            "createdAt" : ISODate("2017-08-30T21:23:21.158Z")
        },
        {
            "updatedAt" : ISODate("2017-08-31T22:24:02.613Z"),
            "createdAt" : ISODate("2017-08-31T22:24:02.613Z"),
            "state" : "revoked"
        },
        {
            "updatedAt" : ISODate("2017-08-31T22:25:02.888Z"),
            "createdAt" : ISODate("2017-08-31T22:25:02.888Z"),
            "state" : "active"
        }
    ]
}
{
    "_id" : ObjectId("59a609778c6929e7122322cf"),
    "user_id" : "9e2cZ",
    "status" : [
        {
            "state" : "active",
            "updatedAt" : ISODate("2017-08-30T21:23:21.158Z"),
            "createdAt" : ISODate("2017-08-30T21:23:21.158Z")
        },
        {
            "updatedAt" : ISODate("2017-08-31T22:24:02.613Z"),
            "createdAt" : ISODate("2017-08-31T22:24:02.613Z"),
            "state" : "revoked"
        },
        {
            "updatedAt" : ISODate("2017-08-31T22:25:02.888Z"),
            "createdAt" : ISODate("2017-08-31T22:25:02.888Z"),
            "state" : "active"
        }
    ]
}

It works, I have 2 users with the same user_id but different statuses.

If I try to insert another active user with the same user_id, it fails :

> db.users.insert({user_id: '9e2cZ', status: [{state: 'active'}]})
WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error collection: test.users index: user_id_1_status.0.state_1 dup key: { : \"9e2cZ\", : \"active\" }"
    }
})

This way, you have an unique index based on the first element of an array.