Cosmos DB Patch child object

Question

I'm trying to patch a child object of a Cosmos Db document using the new Partial Document Update feature. However, I can't find a way to do it.

If the document looks like below, how would you update child (in the Items array) with ID 2 ProductCode property to "A-111" using the new Patch method?

{
    "id": "SalesOrder2",
    "ponumber": "PO15428132599",
    "OrderDate": "2005-07-01T00:00:00",
    "DueDate": "2005-07-13T00:00:00",
    "ShippedDate": "2005-07-08T00:00:00",
    "AccountNumber": "Account2",
    "SubTotal": 6107.082,
    "TaxAmt": 586.1203,
    "Freight": 183.1626,
    "TotalDue": 4893.3929,
    "DiscountAmt": 1982.872,
    "Items": [
        {
            "Id": 1,
            "OrderQty": 3,
            "ProductCode": "A-123",
            "ProductName": "Product 1",
            "CurrencySymbol": "$",
            "CurrencyCode": "USD",
            "UnitPrice": 17.1,
            "LineTotal": 5.7
        },
        {
            "Id": 2,
            "OrderQty": 2,
            "ProductCode": "A-456",
            "ProductName": "Product 2",
            "CurrencySymbol": "$",
            "CurrencyCode": "USD",
            "UnitPrice": 10,
            "LineTotal": 20
        }
    ],
    "_rid": "BsMkAMc43s4CAAAAAAAAAA==",
    "_self": "dbs/BsMkAA==/colls/BsMkAMc43s4=/docs/BsMkAMc43s4CAAAAAAAAAA==/",
    "_etag": "\"00000000-0000-0000-e136-0dbec04601d7\"",
    "_attachments": "attachments/",
    "_ts": 1637760030
}

Answer 1

You need to use the Replace Operation by passing the specific index of the object that you want to update,

in this case, it would be something like,

 ItemResponse<SalesOrder> response = await container.PatchItemAsync<SalesOrder>(
 id: "SalesOrder2",
 partitionKey: new PartitionKey("/SalesOrder2"),
 patchOperations: new[] { PatchOperation.Replace("/Items/1/ProductCode","A-111") });

Answer 2

Unfortunately, there isn't a way to use some sql like syntax to update a specific child array item using a condition. You have to know the index to it. That means you need to pull down your document (you can select only the fields you need to do this work).

This is verbose, but it's really not doing a whole lot once you read through it.

Document:

{
"id": "15bab994-6ea2-436c-badd-a31f44d2e85d",
"conversationId": "5663d3ff-1347-4413-a584-9c634425c7ab",
"subject": "Office credit card",
"dateSent": "2022-06-08",
"dateCreated": "2022-06-06",
"isRetracted": false,
"parentId": "",
"isDeletedForSender": true,
"sender": {
    "emailAddress": "[email protected]",
    "isUnread": true
},
"recipients": [
    {
        "id": 2,
        "emailAddress": "[email protected]",
        "type": 2,
        "isUnread": true,
        "labelIds": [
            2
        ]
    },
    {
        "id": 1,
        "emailAddress": "[email protected]",
        "type": 1,
        "isUnread": true,
        "labelIds": [
            1
        ]
    }
],
"attachments": [
    {
        "name": "card.pdf",
        "sizeInBytes": 129323
    }
],
"_rid": "zl9jANa86aABAAAAAAAAAA==",
"_self": "dbs/zl9jAA==/colls/zl9jANa86aA=/docs/zl9jANa86aABAAAAAAAAAA==/",
"_etag": "\"0d0093b8-0000-0100-0000-62a57c470000\"",
"_attachments": "attachments/",
"recipients[0].isUnread": true,
"_ts": 1655012423
}

Conditional patch code to update isUnread for sender or recipients that match a specific email.

var container = _cosmosClient.GetContainer("my-database", "Message");

    // Get all of the messages that match the list of conversations to update
    var queryResultSetIterator = container.GetItemLinqQueryable<Message2>()
        .Select(x => new PatchMessageContainer
        {
            Id = x.Id,
            ConversationId = x.ConversationId,
            Recipients = x.Recipients,
            Sender = x.Sender
        })
        .Where(x => payload.ConversationIds.Contains(x.ConversationId)).ToFeedIterator();
    var conversations = new List<PatchMessageContainer>();
    while (queryResultSetIterator.HasMoreResults)
        conversations.AddRange(await queryResultSetIterator.ReadNextAsync().ConfigureAwait(false));

    // Set isUnread for the authenticated user in the recipients list
    var updateTasks = new List<Task>();
    foreach (var conversation in conversations)
    {
        // The following Patch operations are smart enough to skip if the target value is already set to the desired value
        var patchOpts = new List<PatchOperation>();
        // Update sender isUnread if sender matches auth email
        if (conversation.Sender.EmailAddress == userClaims.Email && conversation.Sender.IsUnread != payload.SetIsUnread)
            patchOpts.Add(PatchOperation.Set($"/sender/isUnread", payload.SetIsUnread));

        // Update recipient isUnread where recipient matches auth email
        var idx = conversation.Recipients.FindIndex(x => x.EmailAddress == userClaims.Email);
        if (idx > -1 && conversation.Recipients[idx].IsUnread != payload.SetIsUnread)
            patchOpts.Add(PatchOperation.Set($"/recipients/{idx}/isUnread", payload.SetIsUnread));

        // Send the actual patch request
        var task = container.PatchItemAsync<Message2>(conversation.Id.ToString(),
               new PartitionKey(conversation.ConversationId.ToString()), patchOpts);
        updateTasks.Add(task);
    }

    await Task.WhenAll(updateTasks);

conversations variable here is really just the Message document in the first code block, but I've only selected a subset of fields needed to accomplish the update.

Hope this helps.