Conditional upserts in bulk write

Gabriel_Almeida · 2025-03-25T09:27:09.897Z

Hi, everyone. I’m currently working on a not very common use case.

Our MongoDB collection has a unique index composed of four fields. Needless to say those are immutable for a single document. Another important thing is that we can have multiple concurrent writers for the same set of documents. Application-wise, those are distinct flows, one is ran by schedule and the other is ran by request. They might run at the same time since the scope of work of the one by request is much smaller than the other, causing it to be much quicker.

Most fields that are not part of the index can be updated with different values. To achieve that, I’m using a bulk write operation with multiple update models (upserts) that contain a $set aggregation of the document to be upserted. This aggregation uses multiple $cond and $ifNull operations to guarantee that some fields are only updated under certain conditions and others are only written once (when the document is created), respectively.

Currently each upsert model is filtered by the four fields that compose the unique index, but I wanted to add another one without causing unique violations. Since there might be multiple writers running concurrently, each document has a field that has a timestamp, let’s call it updatedAt, and I only want to update documents where the value in the updatedAt field is less than the one that’s being provided by the application. Whenever I add said filter and there’s no match due to updatedAt field in the collection having a value greater than the one I’m trying to update, it will try to insert the document in the collection and return a unique violation.

I know I can achieve this via the aggregation itself by conditionally setting the value of each field based on updatedAt, but that would not be ideal for performance reasons (a bulk write batch can have hundreds of thousands of documents and the document itself is considerably large).

Is there a more elegant way to achieve this?

Thank you all in advance

Gabriel_Almeida · 2025-03-25T14:33:36.753Z

Another potential solution would be to have two update models for a single document, one with upsert set to false along with filtering by the indexed fields + updatedAt less than the provided input and another with upsert set to true, filtering by the indexed fields and a $setOnInsert document.

I’m not very fond of this solution, but I find it better than using conditional updates on every field.