如何在Cosmos DB中编写满足多属性配对约束的查询？

我们在Cosmos DB容器中存储基于以下C#类的房间数据：

public abstract class MyItemBase
{
    public Guid Id { get; set; }
    public string PartitionKey { get; set; } = "some-customer-id-room";
    public Guid RevisionsId { get; set; }
    public int Revision { get; set; }
    public DateTime CreatedAt { get; set; }
    public Guid CreatedBy { get; set; }
    public bool IsDeleted { get; set; }
}

public class MyRoom : MyItemBase
{
    public string Name { get; set; }
    public int Capacity { get; set; }
}

房间的生命周期通过版本控制管理：每次变更都创建新条目，同一房间的所有条目共享RevisionsId，且Revision值逐次递增；通过切换IsDeleted标记实现删除与恢复。

单个房间的生命周期示例：

var sharedRevisionsId = Guid.NewGuid();
var roomChanges = new List<MyRoom>
{
    new() { Id = Guid.NewGuid(), RevisionsId = sharedRevisionsId, Revision = 1, CreatedAt = new DateTime(2024, 2, 16, 17, 0, 0), CreatedBy = Guid.NewGuid(), IsDeleted = false, Name = "Main Hall", Capacity = 100 },
    new() { Id = Guid.NewGuid(), RevisionsId = sharedRevisionsId, Revision = 2, CreatedAt = new DateTime(2024, 2, 17, 13, 0, 0), CreatedBy = Guid.NewGuid(), IsDeleted = false, Name = "Main Hall", Capacity = 90 },
    new() { Id = Guid.NewGuid(), RevisionsId = sharedRevisionsId, Revision = 3, CreatedAt = new DateTime(2024, 2, 18, 11, 0, 0), CreatedBy = Guid.NewGuid(), IsDeleted = true, Name = "Main Hall", Capacity = 90 },
    new() { Id = Guid.NewGuid(), RevisionsId = sharedRevisionsId, Revision = 4, CreatedAt = new DateTime(2024, 2, 19, 19, 0, 0), CreatedBy = Guid.NewGuid(), IsDeleted = false, Name = "Main Hall", Capacity = 90 },
    new() { Id = Guid.NewGuid(), RevisionsId = sharedRevisionsId, Revision = 5, CreatedAt = new DateTime(2024, 2, 20, 20, 0, 0), CreatedBy = Guid.NewGuid(), IsDeleted = true, Name = "Main Hall", Capacity = 90 },
};

为找出分区内当前处于删除状态的房间，我们先执行以下查询获取所有曾标记为删除的候选条目：

SELECT
  root.revisionsId,
  MAX(root.revision) as revision
FROM
  root
WHERE
  root.isDeleted
GROUP BY
  root.revisionsId

但这些候选条目未必是对应房间的最新版本（可能已被恢复），因此需要第二次查询获取这些候选的最新版本：

SELECT
  root.revisionsId,
  MAX(root.revision) as revision
FROM
  root
WHERE
  ARRAY_CONTAINS(@revisionsIds, root.revisionsId)
GROUP BY
  root.revisionsId

最后通过客户端LINQ筛选出当前处于删除状态的条目（即候选的删除版本等于最新版本）：

var deletedItems = deletedCandidates
    .Join(highestRevisions,
        deleted => deleted.RevisionsId,
        highest => highest.RevisionsId,
        (deleted, highest) => (deleted, highest))
    .Where(pair => pair.deleted.Revision == pair.highest.Revision)
    .Select(pair => pair.highest)
    .ToList();

上述筛选结果仅包含RevisionsId和Revision属性，我们需要获取这些条目的完整文档（至少包含Id）。已知目标条目满足：匹配指定的多组(RevisionsId, Revision)配对，且IsDeleted=true，但之前尝试的查询写法均无效：

无效写法1：

SELECT
  *
FROM
  root
WHERE
  ARRAY_CONTAINS(
    [{"revisionsId":"abc...", "revision":5}, {"revisionsId":"123...", "revision":13}],
    { root.revisionsId, root.revision }
  )

无效写法2：

SELECT
  *
FROM
  root
WHERE
  ARRAY_CONTAINS(
    ["abc...", "123..."],
    root.revisionsId
  )
ORDER BY root.revision desc

方法1：通过JOIN匹配多组属性配对 #

使用参数化的配对数组，通过JOIN语句匹配每个条目的RevisionsId和Revision：

SELECT *
FROM root
JOIN pair IN @revisionPairs
WHERE root.revisionsId = pair.revisionsId 
  AND root.revision = pair.revision 
  AND root.isDeleted = true

其中参数@revisionPairs的格式为：

[
  {"revisionsId": "abc...", "revision": 5},
  {"revisionsId": "123...", "revision": 13}
]

这种方式相比ARRAY_CONTAINS更可靠，因为ARRAY_CONTAINS对对象的匹配要求严格的结构一致性（包括属性顺序），而JOIN直接通过等值条件匹配属性，避免了结构匹配的问题。

方法2：单查询直接获取当前删除的完整文档 #

如果想跳过多次查询和客户端处理的步骤，可以用子查询直接获取每个房间的最新版本，再筛选IsDeleted=true的条目：

SELECT *
FROM root
WHERE root.revision = (
    SELECT MAX(r.revision)
    FROM r
    WHERE r.revisionsId = root.revisionsId
)
AND root.isDeleted = true

这个查询会直接返回所有当前处于删除状态的完整房间文档，无需额外的客户端处理，效率更高。

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