I have a DB called participants with structure:
{
_id: '123',
infos: [
{
myId: 'my123',
otherId: 'other123'
}
]
}
However, studyInfos can be empty and the objects inside of studyInfos can contain a myId without a otherId. I want to create a unique index on
{ myId: 1, otherId: 1}
I am using ensureIndex like so:
Participants._ensureIndex(
{ 'infos.myId': 1, 'infos.otherId': 1 },
{
unique: true,
sparse: true,
partialFilterExpression: {
'infos.otherId': { $exists: true, $type: String, $ne: null },
'infos.myId': { $exists: true, $type: String, $ne: null },
},
},
);
However, when i try to add a second user with infos empty or two users with otherId missing in both it fails due to duplicate index. Basically the only duplicate I don't want is
{
_id: '123',
infos: [
{
myId: 'my123',
otherId: 'other123'
}
]
},
{
_id: '456',
infos: [
{
myId: 'my123',
otherId: 'other123'
}
]
},
But its not allowing:
{
_id: '123',
infos: []
},
{
_id: '456',
infos: []
},
or
{
_id: '123',
infos: [
{
myId: 'my123',
}
]
},
{
_id: '456',
infos: [
{
myId: 'my123',
}
]
},
either despite my usage of partialFilterExpression and sparse.
Help.
Answering my own question, it was two things:
Final Answer:
Participants.rawCollection().createIndex(
{ 'infos.myId': 1, 'infos.otherId': 1 },
{
unique: true,
partialFilterExpression: {
'infos.otherId': { $exists: true },
'infos.myId': { $exists: true },
},
},
);