Cosmos SQL upsert not working as expected

5
votes

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);.

1
javaazure-cosmosdbazure-cosmosdb-sqlapi
Is your collection partitioned? Are you sending the same id and partition key value on each upsert? – Matias Quaranta
the id is automatically assigned by setting the false flag on the upsertDocument method. the partition key is the same for both requests and the unique field is the same for both requests. I want identical requests to upsert as we have 2 separate clouds attempting to upsert the same request. – Artanis
Documents are uniquely identified by their Id and Partition Key Value, regardless of Unique Field configuration. Unique Fields are there to add an extra check. If you send an Upsert with a random "id" every time, then Upsert will always create a different document. For Upsert to do an update/replace, you need to provide the id of the document as part of the request and not an autogenerated one (which would make it different each time). – Matias Quaranta
but the exception its giving me is that the id is the same. is that just a misleading effect of using a randomly generated id with matching unique values? – Artanis
So you are correct I needed to define the id myself and use the exact same id for the other requests. This is the exact opposite of what the exception message said, which is pretty misleading. Please post an answer and I'll mark it as correct, thank you! – Artanis

1 Answers

6
votes

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).