28

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?

Improve this question
1
  • FYI related to your question (though not exactly the same) - an answer going into more detail regarding select vs update / insert / delete along with sdk calls to deal with those (though the updates done via SDK are still done as full document replacement, as Nick pointed out in his answer) - stackoverflow.com/a/46873986/272109 Commented May 16, 2019 at 22:21

5 Answers 5

Reset to default
27

Original Answer, as of 2019

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.

2024 Update

Yes, you can "patch" your item to only update a single field. Note that there are six operations in the patch API: add, set, replace, remove, increment and move

This is official known as a Partial Document Update

The Docs

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You could use a stored procedure. E.g., this sproc is similar to MongoDB's update operator.
A private preview is now under way. You can sign up here aka.ms/cosmosdbpatch
Starting with November 2021 partial document updates are now possible with .NET, Java, and Node SDK, as well as with stored procedures.
8

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!

UPDATE :

CosmosDB support for Partial Update is GAed. You can read more from here

Improve this answer

2 Comments

"is working" might not be the most accurate state, it has been 1 year and 7 months since and still nothing
this is so behind, even MongoDB can run updateMany command
3

I know it's been a while since the original question was posed, but this entry is the primary search result.

We can now use the following operation to update only part of the record.

Container.PatchItemAsync

https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.container.patchitemasync?view=azure-dotnet

Improve this answer

Comments

1

As of 25.05.2021 this feature is available in private preview and will hopefully be in GA soon.

https://azure.microsoft.com/en-us/updates/partial-document-update-for-azure-cosmos-db-in-private-preview/

Improve this answer

Comments

0

You can use Patch API for partial updates. Refer: https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update

Improve this answer

1 Comment

That requires a slightly different auth token to achieve. I would say this should not be the answer. The question here is probably a user talking about using SDK access with queries rather than Azure API.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.