Azure Cosmos DB SQL API UPDATE statement - don't replace whole document
Solution 1
The Cosmos DB SQL language only supports the Select
statement.
Partially updating a document isn't supported via the sql language or the SQL API.
People have made it clear that that's a feature they want so there is a highly upvoted request for it that can be found here: https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/6693091-be-able-to-do-partial-updates-on-document
Microsoft has already started to work on that so the only thing you can do is wait.
Solution 2
To elaborate more on this, Updating partially a document in CosmosDb
on the server isn’t possible, instead you can do whatever you need to do in the memory.In order to literally UPDATE
the document, you will have to to retrieve the entire document from the CosmosDb, update the property/properties
that you need to update and then call the ‘Replace’ method in the CosmosDb SDK to replace the document in question. Alternatively you can also use ‘Upsert’ which checks if the document already exists and performs an ‘Insert’
if true or ‘Replace’ if false.
NOTE : Make sure you have the latest version of the document before you commit an update!
As Nick mentioned Cosmosdb team is working on the partial update.
UPDATE :
CosmosDB will support Partial Update is GAed. You can read more from here
Solution 3
As of 25.05.2021 this feature is available in private preview and will hopefully be in GA soon.
![Scotty H](https://i.stack.imgur.com/3OZ7M.jpg?s=256&g=1)
Comments
-
Scotty H almost 2 years
Can I write an
UPDATE
statement for Azure Cosmos DB? The SQL API supports queries, but what about updates?In particular, I am looking to update documents without having to retrieve the whole document. I have the ID for the document and I know the exact path I want to update within the document. For example, say that my document is
{ "id": "9e576f8b-146f-4e7f-a68f-14ef816e0e50", "name": "Fido", "weight": 35, "breed": "pomeranian", "diet": { "scoops": 3, "timesPerDay": 2 } }
and I want to update
diet.timesPerDay
to 1 where the ID is"9e576f8b-146f-4e7f-a68f-14ef816e0e50"
. Can I do that using the Azure SQL API without completely replacing the document? -
pablete almost 4 yearsUser specified SQL API
-
user2780436 almost 4 yearsThe question specifies nothing about mongo db
-
Grzegorz Pawlik over 3 years"is working" might not be the most accurate state, it has been 1 year and 7 months since and still nothing
-
Stephen Cleary over 3 yearsYou could use a stored procedure. E.g., this sproc is similar to MongoDB's
update
operator. -
Gonkers about 3 yearsA private preview is now under way. You can sign up here aka.ms/cosmosdbpatch
-
Neits over 2 yearsStarting with November 2021 partial document updates are now possible with .NET, Java, and Node SDK, as well as with stored procedures.