Cosmos SQL upsert not working as expected

Question

If I try to upsert a document with an id and partition key that already exists, it works as expected IF there are no Unique constraints set on the container.

However, when I set any value in the table as Unique, then the upsert doesn't work and I get a (409 conflict - a Document with an id matching the id field of document already exists). The Unique constraint shouldn't be a problem in this case, but it causes this error, which has a strange error description for an upsert because it shouldn't matter if the id already exists.

I'm using documentClient.upsertDocument(collectionLink, documentDefinition, null, true);.

Answer 1

Cosmos DB's Upsert / Replace flow is based on the document's unique identifier (id and Partition Key value), Unique Fields are there to add extra checks for data validation.

When an Upsert operation is made, the service will look for an existing document with the same id and Partition Key value, if there is one, it will update it, otherwise, it will create it.

In your case, you are always sending a new random id, so Upsert is not finding an existing document to update and it's creating a new one every time.

Please define your own id before initiating the Upsert, and set the autogenerate id attribute in the upsert call to it's default (true) value: client.upsertDocument(collectionLink, documentDefinition).