CODEWITHSUNDEEP
c#azureazure-cosmosdbazure-cosmosdb-sqlapi
Paul
Paul

Reputation: 3283

Avoid UpsertAsync in CosmosDB - run update SQL command

I have the structure below in a Cosmos collection (where this structure will not be changed)

{
    "attribute1": "",
    "attribute2": "",
    "attribute3": "11181",
    "attribute4": "Text value",
    "companies": [
        {
            "companyId": "Guid",
            "companyName": "Value"
        }
    ],
    "id": "ec4c18d9-178d-43d4-a2b0-093b160bfd06"
}

I have a process which modifies the company list

Is there any way of running a "UPDATE" statement in Cosmos to update the Companies list only?

The current implementation shown below takes 1 minute which is quite a lot longer than I would like

public void RandomizeCompanies()
{
  foreach (var item in _items)
  {
      //Clear companies list and add random elements to the list
  }
}

public async Task ChangeCompaniesAsync()
{
    await ConnectToDatabaseAsync();
    await GetItemsFromCosmosAsync().ConfigureAwait(false);
    RandomizeCompanies();

    try
    {
        Console.WriteLine($"Starting...");
        var stopwatch = Stopwatch.StartNew();
        var tasks = new List<Task>(_items.Count);

        foreach (var item in _items)
        {
            tasks.Add(_cosmosConnection.Container.UpsertItemAsync(item, new PartitionKey(item.Id)));
        }
        await Task.WhenAll(tasks);
        stopwatch.Stop();

        Console.WriteLine($"Finished writing {_items.Count} items in {stopwatch.Elapsed}.");
    }
    catch (Exception ex)
    {
         Console.WriteLine(ex);
    }
    finally
    {
        Console.WriteLine("Cleaning up resources...");
    }
  }
}

Something like

var tasks = new List<Task>(_items.Count);
foreach (var item in _items)
{
    tasks.Add( Run update statement that updates companies list to be items.Companies  );
}
await Task.WhenAll(tasks);

The current implementation is not ideal because I am serializing all of the document even though only a small part is being changed.

Upvotes: 0

Views: 1685

Answers (2)

Mark Brown
Mark Brown

Reputation: 8763

As noted in another answer, partial updates are not currently supported. However, even once supported it is quite possible this will not be as efficient as if you had designed your model to separate static from frequently changing data. This is especially true for large document sizes with deep nested structure. These modeling suggestions below include using a "type" property to distinguish the entity type and with a shared "partition key" and "id" so they can be queried together. There are two primary scenarios to consider.

Scenario 1 (static properties and properties with frequent updates): In this scenario you have a large set of static properties and a smaller number of frequently updated properties. In this scenario model the static properties as a single document and the frequently updated properties as a second (smaller) document with the same partition key and id. If it is not smaller, separate more frequently from less frequently updated properties.

Scenario 2 (static properties and properties in unbounded array): In this scenarios you have the same large set of static properties but an ever growing list of properties in an array. This doesn't have to be specifically unbounded but when you have an array that typically keeps grows it is more efficient to make each element in that array as it's own document with shared partition key and id, and simply insert into the container.

For more information on some of these concepts see, Data modeling in Azure Cosmos DB

Hope this is helpful.

Upvotes: 2

RCT
RCT

Reputation: 1072

@Paul Partial document updates are not possible in Cosmos SQL API. This is a feature request on user voice. https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/6693091-be-able-to-do-partial-updates-on-document

Upvotes: 1

Related Questions