How to set a field for every document in a Cosmos db?

user_131270 0
Viewed 732

What would a Cosmos stored procedure look like that would set the PumperID field for every record to a default value?

We are needing to do this to repair some data, so the procedure would visit every record that has a PumperID field (not all docs have this), and set it to a default value.

enter image description here

azure-cosmosdb
2 Answers

For scenarios where you want to iterate through every item in a container and update a property, the best means to accomplish this is to use the Change Feed Processor and run the operation in an Azure function or VM. See Change Feed Processor to learn more and examples to start with.

With Change Feed you will want to start it to read from the beginning of the container. To do this see Reading Change Feed from the beginning.

Then within your delegate you will read each item off the change feed, check it's value and then call ReplaceItemAsync() to write back if it needed to be updated.

static async Task HandleChangesAsync(IReadOnlyCollection<MyType> changes, CancellationToken cancellationToken)
{
    Console.WriteLine("Started handling changes...");
    foreach (MyType item in changes)
    {
        if(item.PumperID == null)
        {
            item.PumperID = "some value"
            //call ReplaceItemAsync(), etc.
        }
    }

    Console.WriteLine("Finished handling changes.");
}
user_8849310

Assuming a one-time data maintenance task, arguably the simplest solution is to create a single purpose .NET Core console app and use the SDK to query for the items that require changes, and perform the updates. I've used this approach to rename properties, for example. This works for any Cosmos database and doesn't require deploying any stored procs or otherwise.

Ideally, it is designed to be idempotent so it can be run multiple times if several passes are required to catch new data coming in. If the item count is large, one could optionally use the SDK operations to scale up throughput on start and scale back down when finished. For performance run it close to the endpoint on an Azure Virtual Machine or Function.

user_24117130
Related