如何在Cosmos DB/SQL中查询满足双版本属性条件的文档ID？

我有一个包含Version和Tier属性的Cosmos DB文档数据库，示例文档如下：

{
    "partitionKey": "AEDlCE4k4Xl/hv6o7ZNtSX9B5XjtfOKxYDCgGbQB518=",
    "id": "1055984d-01bd-4fbf-877f-0c18ce91552e",
    "Version": 1,
    "Tier": "basic"
},
{
    "partitionKey": "ABClCE4k4Xl/hv6o6ZNtSX9B5XjtfOKxYDCgGbQB424=",
    "id": "1055984d-01bd-4fbf-877f-0c18ce91552e",
    "Version": 2,
    "Tier": "standard"
}

需要查询出**同时存在Version=1且Tier="basic"、Version=2且Tier="standard"**的文档ID。

我尝试了如下查询语句，但未返回任何结果：

select c.id from c
where (c.Version = 1 and c.TierId = "basic")
AND (c.Version = 2 and c.TierId = "standard")

原因很明显：原查询试图在单个文档中同时匹配两个互斥条件（一个文档不可能同时有Version=1和Version=2），所以无法得到结果。

解决方案：使用GROUP BY + HAVING筛选分组 #

正确的思路是先筛选出符合任一条件的文档，再按id分组，最后确保分组内同时存在两种符合条件的文档。查询语句如下：

SELECT c.id
FROM c
WHERE (c.Version = 1 AND c.Tier = "basic") OR (c.Version = 2 AND c.Tier = "standard")
GROUP BY c.id
HAVING COUNT(CASE WHEN c.Version = 1 AND c.Tier = "basic" THEN 1 END) > 0
   AND COUNT(CASE WHEN c.Version = 2 AND c.Tier = "standard" THEN 1 END) > 0

逻辑说明： #

1. WHERE子句先过滤出所有满足Version=1且Tier=basic或者Version=2且Tier=standard的文档；
2. GROUP BY c.id将相同id的文档归为一组；
3. HAVING子句通过条件计数，确保该分组内同时存在两种符合要求的文档：
   • 第一个COUNT统计分组内符合Version=1且Tier=basic的文档数量，需大于0；
   • 第二个COUNT统计分组内符合Version=2且Tier=standard的文档数量，需大于0。

补充说明：由于数据库使用独立于id的partitionKey，该查询会跨分区执行，若数据量较大，需注意查询性能优化。

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