Cannot use $merge pipeline stage with a unique index with partialFilter expression

I mix different types of images in my collection. Each document type calls for a different set of unique ids. 2D images have their own identifyeers, that they do not sharre with the identifiers for 3D images.
Here is a simplified overview of the documents in the collection :

{ _id : ...
, type : 2D'
, 2DimageID : 12345}
{
_id : ...
, type : '3D',
, 3DimageID : 5678
}

I am trying to use $merge on this partial and unique index
db.images.createIndex({'type': 1, '3DimageID': 1},{ partialFilterExpression: { 'type': '3D' }, unique : true})
But get this error:
OperationFailure: Cannot find index to verify that join fields will be unique, full error: {'operationTime': Timestamp(1639405564, 1), 'ok': 0.0, 'errmsg': 'Cannot find index to verify that join fields will be unique', 'code': 51183, 'codeName': 'Location51183', '$clusterTime': {'clusterTime': Timestamp(1639405564, 1), 'signature': {'hash': b'\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00', 'keyId': 0}}}

Am I missing something here ?

First, please do not publish documents with

It forces us to edit the document before we are able to insert into our environment. Leave _id out or publish real ids. It help us help you and help more people because it is more efficient time wise.

Since your partialFilterExpression specifies type:3D, having type:1 in your index is redundant and wasteful space wise. I would only specify 3DmageID in the index.

And it seems to work:

c.find().projection({_id:0})
{ type: 2, '2d_image_id': 33 }
{ type: 3, '3d_image_id': 33 }
{ type: 4, '4d_image_id': 55 }
//  Unique index by removing type:1
c.createIndex( { "3d_image_id" : 1 }, { partialFilterExpression : { 'type' : 3 } , unique : true } )
// Same thing with $exists
c.createIndex( { "4d_image_id" : 1 }, { partialFilterExpression : { '4d_image_id' : { "$exists" : true } } , unique : true } )
// Which brings the subject of sparse indexes
c.createIndex( { "2d_image_id" : 1 }, { sparse : true  , unique : true } )
// Resulting indexes
c.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2,
    unique: true,
    key: { '3d_image_id': 1 },
    name: '3d_image_id_1',
    partialFilterExpression: { type: 3 }
  },
  {
    v: 2,
    unique: true,
    key: { '4d_image_id': 1 },
    name: '4d_image_id_1',
    partialFilterExpression: { '4d_image_id': [Object] }
  },
  {
    v: 2,
    unique: true,
    key: { '2d_image_id': 1 },
    name: '2d_image_id_1',
    sparse: true
  }
]

See https://docs.mongodb.com/manual/core/index-sparse/

Thanks Steeve, and sorry about the _ids, as I’m working with patient data, I’m always (too) cautious.
Actually, the Index creation is not a problem, rather, the $merge pipeline stage refuses to use the created index, altough unique, to perform the merge operation.
I finally found out that partial unique index is not enough, although it wasn’t specified in the official documentation (see the documentation clarification request from MongoDb dev here : https://jira.mongodb.org/browse/DOCS-14827 )

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.