How to perform bulk updates with CosmosDB Core API similar to UPDATE command in SQL?

John Glista 0

We have a CosmosDB Core API instance and are finding it difficult to perform one-off bulk updates of our data, similar to how you use the UPDATE .... SET X = Y command with a relational SQL database. The initial research I performed pointed me in the direction of using Core API over the others because we are not migrating from MongoDB, do not need a graph DB, and wanted all the latest features that the platform has to offer. However, I'm now wishing we would have gone with the MongoDB API as it looks to be very easy to use a Mongo "scratch pad" for executing these types of updates.

Is it only possible to perform these types of one-off bulk updates by creating a stored procedure inside the Azure Data Explorer? This seems extremely inconvenient for simple data updates, and if that is true, I'm surprised Microsoft hasn't released an easier way to do this. Is there an archive of examples somewhere to at least make using stored procedures easier?

Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2023-09-06T17:03:05.3266667+00:00

@John Glista Thank you for reaching out.

There is no UPDATE in Cosmos DB (Bulk or no Bulk). The closest thing is Patch. You can use transactional batch in our SDKs. This article provides examples that illustrate how to use Partial Document Update with .NET, Java, and Node SDKs.

Get started with Azure Cosmos DB Partial Document Update

Regards,

Oury
Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2023-09-08T16:30:38.0666667+00:00

@John Glista Checking if the answer was helpful. If yes, please don't forget to mark as accept answer. Otherwise comment below if you need additional clarification.

Regards,

Oury
John Glista 0 Reputation points

2023-09-08T17:37:23.19+00:00

Unfortunately that was not the answer I was hoping for. Even using server-side programming with a stored procedure, it's quite a bit of code just to perform a simple update of some documents (when compared to either SQL UPDATE or a MongoDB API update() command).
Mark Brown - MSFT 2,766 Reputation points Microsoft Employee

2023-09-08T20:42:54.1066667+00:00
@John Glista Updating an item is very straightforward. I would not use our Stored Procedures to do this. They are cumbersome to write and debug. Using the CRUD operations using any of the Cosmos SDK's is much easier.

The code to create a new object, insert it and update it can be done with just a few lines of code. As an example:

// </create_object> Product item = new( id: "68719518388", category: "gear-surf-surfboards", //partition key name: "Sunny Surfboard", quantity: 8, sale: true ); // <create_item> Product createdItem = await container.CreateItemAsync<Product>( item: item, partitionKey: new PartitionKey(item.category) ); // <change something> item.quantity = 10; // <update_item> Product replacedItem = await container.ReplaceItemAsync<Product>( item: item, id: item.id, partitionKey: new PartitionKey(item.category) );

If you're new to Cosmos DB and want a handy API reference, this GitHub repo can be helpful.

https://github.com/Azure-Samples/cosmos-db-nosql-dotnet-samples
John Glista 0 Reputation points

2023-09-11T12:54:33.02+00:00

Mark, we are not having any issues updating documents from within our code (Spring Boot services). In my question I specifically called out "one-off" bulk updates, i.e. a situation where some data in prod needs corrected but we don't want to code, build, and deploy an entire stand-alone application just to do that. This is a situation where with a relational database, you'd use a tool such as DataGrip, pGAdmin, DBeaver, etc. to perform a one-off UPDATE command to correct that data in multiple rows at a time. I called out the fact that with CosmosDB Mongo API, you can use a scratch pad and the many powerful Mongo commands to serve this purpose, if you are in fact using the Mongo DB API. We are not. We chose the No SQL API because it seemed like the best choice when we stood up our instances, but now knowing that there appears to be no way to easily perform these one-off updates to data in batches, we are considering migrating over to a MongoDB API instance to mitigate this.
Eric Carlson 0 Reputation points

2024-11-25T00:08:14.5833333+00:00

At the company I work at, we have been using Database Pilot, which has a bulk update feature for mass updating Cosmos DB NoSQL data. You write a query for the data you want to update, and then define one or more update operations to perform. We use it a lot when we have to do various schema changes or even mass data fixes, things like that. You can also do mass deletes and creates. It also has various querying features which are helpful.

2 answers

Mark Brown - MSFT 2,766 Reputation points Microsoft Employee

2023-09-18T22:19:11.91+00:00

@John Glista Unfortunately there are not a lot of 3rd party tools created for NoSQL API. SQL has been around for 50+ years so has had time to build a healthy ecosystem of tooling.

The only thing I can think of in your situation is there is this one that does mention the ability to update multiple documents at once. https://alpaqastudio.com/features. Hope that helps.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Eric Carlson 0 Reputation points

2024-11-25T00:10:29.6666667+00:00

I use Database Pilot free desktop app which has a bulk update feature for Cosmos NoSQL.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to perform bulk updates with CosmosDB Core API similar to UPDATE command in SQL?

2 answers

Your answer