Mongo Nested Arrays and (Partial) Unique Indexes

Viewed 138

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?

0 Answers
Related