新手求教：如何在Cosmos DB中执行子文档增删改及Upsert操作

嘿，作为曾经也是Cosmos DB新手的过来人，我太懂你对子文档操作的困惑了！咱们就用你给出的示例文档，一步步解决这四个操作问题：

示例文档：

{ 
  "Id": "1234", 
  "Name": "foo", 
  "Items": [ 
    { 
      "Id": "abcd", 
      "Age": 35, 
      "Claims": [ 
        { "Name": "email", "Value": "foo@bar.com" } 
      ] 
    } 
  ] 
}

要给Items数组新增元素，Cosmos DB提供了ARRAY_APPEND函数，它会生成一个包含原数组所有元素加上新元素的新数组，非常适合这个场景。

SQL 更新语句 #

假设我们要添加一个Id为"efgh"的新项，SQL语句如下：

UPDATE c
SET c.Items = ARRAY_APPEND(c.Items, {
    "Id": "efgh",
    "Age": 28,
    "Claims": [{"Name": "phone", "Value": "123-456-7890"}]
})
WHERE c.Id = "1234"

.NET SDK 高效写法（Patch操作） #

如果你用.NET SDK，推荐用Patch操作，不需要读取整个文档就能完成更新，性能更好：

var patchOperations = new[] {
    PatchOperation.Add("/Items", new {
        Id = "efgh",
        Age = 28,
        Claims = new[] { new { Name = "phone", Value = "123-456-7890" } }
    })
};

await container.PatchItemAsync<dynamic>(
    id: "1234",
    partitionKey: new PartitionKey("1234"),
    patchOperations: patchOperations
);

删除数组中的特定元素，我们可以用ARRAY_FILTER函数过滤掉目标元素，留下其他元素组成新数组。

SQL 更新语句 #

比如要删除Id为"abcd"的Items元素：

UPDATE c
SET c.Items = ARRAY_FILTER(c.Items, item => item.Id != "abcd")
WHERE c.Id = "1234"

.NET SDK 写法 #

用SDK的话，先找到目标元素的索引，再用Patch操作删除：

// 先读取文档找到目标元素的索引
var doc = await container.ReadItemAsync<dynamic>("1234", new PartitionKey("1234"));
var targetIndex = doc.Resource.Items.ToList().FindIndex(i => i.Id == "abcd");

if (targetIndex != -1)
{
    var patchOperations = new[] {
        PatchOperation.Remove($"/Items/{targetIndex}")
    };
    await container.PatchItemAsync<dynamic>("1234", new PartitionKey("1234"), patchOperations);
}

Upsert的逻辑是：如果目标项存在就更新它，不存在就添加它。我们可以结合ARRAY_CONTAINS做存在判断，再用ARRAY_REPLACE或ARRAY_APPEND完成操作。

SQL 更新语句 #

比如要Upsert一个Id为"abcd"的项（把Age更新为36），如果不存在就添加：

UPDATE c
SET c.Items = CASE
    WHEN ARRAY_CONTAINS(c.Items, {"Id": "abcd"}, true)
    THEN ARRAY_REPLACE(c.Items, ARRAY_SLICE(c.Items, 0, 1)[0], {
        "Id": "abcd",
        "Age": 36,
        "Claims": [{"Name": "email", "Value": "foo@bar.com"}]
    })
    ELSE ARRAY_APPEND(c.Items, {
        "Id": "abcd",
        "Age": 36,
        "Claims": [{"Name": "email", "Value": "foo@bar.com"}]
    })
END
WHERE c.Id = "1234"

注：ARRAY_CONTAINS的第三个参数true表示严格匹配整个对象，ARRAY_SLICE用来定位到目标元素。

.NET SDK 写法 #

用SDK的话，逻辑更直观：先检查元素是否存在，再决定更新还是添加：

var doc = await container.ReadItemAsync<dynamic>("1234", new PartitionKey("1234"));
var itemsList = doc.Resource.Items.ToList();
var existingItem = itemsList.FirstOrDefault(i => i.Id == "abcd");

var newItem = new {
    Id = "abcd",
    Age = 36,
    Claims = new[] { new { Name = "email", Value = "foo@bar.com" } }
};

if (existingItem != null)
{
    // 存在则更新
    var index = itemsList.FindIndex(i => i.Id == "abcd");
    itemsList[index] = newItem;
}
else
{
    // 不存在则添加
    itemsList.Add(newItem);
}

doc.Resource.Items = itemsList;
await container.ReplaceItemAsync(doc.Resource, "1234", new PartitionKey("1234"));

这个操作需要先定位到目标Items项，再修改它的Claims数组，我们分两种场景来看：

添加Claim #

比如给Id为"abcd"的项新增一个"address"类型的Claim：

SQL 更新语句 #

UPDATE c
SET c.Items = ARRAY(
    SELECT VALUE CASE
        WHEN item.Id = "abcd" THEN
            {
                "Id": item.Id,
                "Age": item.Age,
                "Claims": ARRAY_APPEND(item.Claims, {"Name": "address", "Value": "123 Main St"})
            }
        ELSE item
    END
    FROM item IN c.Items
)
WHERE c.Id = "1234"

.NET SDK 写法 #

var doc = await container.ReadItemAsync<dynamic>("1234", new PartitionKey("1234"));
var targetItem = doc.Resource.Items.ToList().FirstOrDefault(i => i.Id == "abcd");

if (targetItem != null)
{
    var claimsList = targetItem.Claims.ToList();
    claimsList.Add(new { Name = "address", Value = "123 Main St" });
    targetItem.Claims = claimsList;
    await container.ReplaceItemAsync(doc.Resource, "1234", new PartitionKey("1234"));
}

删除Claim #

比如删除Id为"abcd"的项中Name为"email"的Claim：

SQL 更新语句 #

UPDATE c
SET c.Items = ARRAY(
    SELECT VALUE CASE
        WHEN item.Id = "abcd" THEN
            {
                "Id": item.Id,
                "Age": item.Age,
                "Claims": ARRAY_FILTER(item.Claims, claim => claim.Name != "email")
            }
        ELSE item
    END
    FROM item IN c.Items
)
WHERE c.Id = "1234"

.NET SDK 写法 #

var doc = await container.ReadItemAsync<dynamic>("1234", new PartitionKey("1234"));
var targetItem = doc.Resource.Items.ToList().FirstOrDefault(i => i.Id == "abcd");

if (targetItem != null)
{
    var claimsList = targetItem.Claims.ToList();
    claimsList.RemoveAll(c => c.Name == "email");
    targetItem.Claims = claimsList;
    await container.ReplaceItemAsync(doc.Resource, "1234", new PartitionKey("1234"));
}

内容的提问来源于stack exchange，提问作者Jerrod Horton