I'm using MongoDB v3.2. I have objects in a collection that have the following form:
{
...
root_array: [
{
...
child_array: [
{
id: 'some-uuid'
}
]
...
},
{
...
child_array: [
{
id: 'some-other-uuid'
}
]
...
}
{
...
child_array: [
// possibly empty
]
...
}
]
...
}
I would like to have a unique index on this collection that enforces the constraint that, across the entire collection, no child_array element objects have the same value for the id field. The way I would have wanted to state that simply is:
collection.createIndex({
'root_array.child_array.id': 1
}, {
unique: true
});
... which actually does the job, except for the fact that not all child_arrays are non-empty. So two degenerate objects (with empty one-element root_arrays, containing a zero-element child_array) cause a constraint violation. This would usually lead me to try and specify a partialFilterExpression to relax the constraint. However, I don't see a way to do that in this case. The expression that I would want to state would look something like this:
collection.createIndex({
'root_array.child_array.id': 1
}, {
unique: true,
partialFilterExpression: {
'root_array.child_array': {$gt: ''}
}
});
... but this seems wrong too because the following two objects would likewise cause a constraint violation:
{ root_array: [ { child_array: [ { id: 1 } ] }, { child_array: [] } ] }
{ root_array: [ { child_array: [ { id: 2 } ] }, { child_array: [] } ] }
... because both elements satisfy the partialFilterExpression and both documents have a child_array with no elements (and hence an undefined id for one of their children).
Is it intractable to specify this as a database constraint, and must it be handled as an application-level concern? Or is there some way to get at what I'm saying here in the database, without fundamentally changing the structure of the documents in question?