Failed to target upsert by query :: could not extract exact shard key', details={}}.; nested exception is com.mongodb.MongoWriteException

Question

We have recently moved to mongo java driver core/sync 4.4.0 from 3.12.1 with spring data mongo db from 2.2.5.RELEASE to 3.3.0 also spring boot version 2.6.2 and mongo server version 4.2.5

We are getting exceptions while hitting upsert queries on sharded collection with above mentioned error

There is way to insert shard key in query filter but that is not feasible for us, hence we tried adding @Sharded annotations to our DTO as we have different shard keys for different collections

Still we are getting the above mentioned error also we are unable to get exact meaning of what is full copy of entity meant in below statement

update/upsert operations replacing/upserting a single existing document as long as the given UpdateDefinition holds a full copy of the entity.

Other queries are working fine also upsert queries are working fine on addition of shard key in query filter but that change is not feasible for us we need quick solution

Please help as not able to find any solution on any platform. Thanks in advance!

Answer 1

So here's the deal:

You cannot upsert on Sharded Collection in MongoDB UNLESS you include the shard key in the filter provided for update operation.

To quote MongoDB Docs:

For a db.collection.update() operation that includes upsert: true and is on a sharded collection, you must include the full shard key in the filter:

1. For an update operation.
2. For a replace document operation (starting in MongoDB 4.2).

If you are using MongoDB 4.4+ then you have a workaround as mentioned below:

However, starting in version 4.4, documents in a sharded collection can be missing the shard key fields. To target a document that is missing the shard key, you can use the null equality match in conjunction with another filter condition (such as on the _id field). For example:

{ _id: <value>, <shardkeyfield>: null } // _id of the document missing shard key

Ref: - https://docs.mongodb.com/manual/reference/method/db.collection.update/#behavior