Hi @turivishal
I think you’re hitting the limitations of partial index filter as described in SERVER-17853 where it mentions that some features like $elemMatch are not yet supported (which I think is a requirement for your use case).
For a small demonstration, I created an index exactly like yours:
> db.coll.createIndex({'members.userId':1},
{unique:true,
partialFilterExpression: {'members.userId': {$type: 'string'}}})
members.userId_1
Then I inserted a document with a string and a number as members.userId
:
> db.coll.insertOne({members:{userId:'abc'}})
> db.coll.insertOne({members:{userId:123}})
So the first document should be in the index, while the second document should not:
> db.coll.find().hint('members.userId_1')
[
{
_id: ObjectId("60d180f635b7b416ce4312a5"),
members: { userId: 'abc' }
}
]
The hint()
forces MongoDB to use that index, which shows that the first document does exist in the index, while the second one doesn’t. In this case, it behaves as expected.
However, once we change members
into an array, it behaves a bit differently:
> db.coll.insertOne({members:[{userId:'def'}, {userId:123}]})
> db.coll.insertOne({members:[{userId:'ghi'}, {userId:123}]})
MongoError: E11000 duplicate key error collection: test.coll index: members.userId_1 dup key: { members.userId: 123 }
So it’s complaining about a duplicate key of 123
which technically should not be in the index, but it actually is:
> db.coll.find({'members.userId':123}).hint('members.userId_1')
[
{
_id: ObjectId("60d1811835b7b416ce4312a8"),
members: [ { userId: 'def' }, { userId: 123 } ]
}
]
This is because I think you need $elemMatch for the partial filter expression (since it involves an array, and you’re matching one element of the array), where it’s not supported yet.
As a workaround, my suggestion is to not use an array for now (until SERVER-17853 is resolved). Having said that, if this feature is important for your workflow, please comment/vote on the aforementioned SERVER-17853 describing your use case.
Best regards
Kevin