如何在Azure Cosmos DB中获取分组文档的最大值记录？

需求说明 #

我需要用Azure Cosmos DB的SQL API查询文档，要求先过滤文档，再按指定字段分组，最后返回每组中某字段值最大的完整文档。

示例文档 #

Cosmos DB中的示例文档如下：

{
  "id": "1",
  "someValue": "shall be included",
  "group": "foo",
  "timestamp": "1668907312"
}

{
  "id": "2",
  "someValue": "shall be included",
  "group": "foo",
  "timestamp": "1668907314"
}

{
  "id": "3",
  "someValue": "shall be included",
  "group": "bar",
  "timestamp": "1668907312"
}

{
  "id": "4",
  "someValue": "don't include",
  "group": "bar",
  "timestamp": "1668907312"
}

查询要求 #

需获取满足以下条件的文档：

• someValue等于"shall be included"
• 按group字段分组
• 仅返回每组中timestamp值最大的文档

期望结果 #

最终返回的文档应为：

{
  "id": "2",
  "someValue": "shall be included",
  "group": "foo",
  "timestamp": "1668907314"
},
{
  "id": "3",
  "someValue": "shall be included",
  "group": "bar",
  "timestamp": "1668907312"
}

当前问题 #

需要实现满足以下要求的最优方案：

• 单条查询完成所有逻辑
• 支持配合Azure SDK使用SqlParameter防止SQL注入

我的尝试 #

目前采用两次查询的方式实现，但无法为文档路径使用SqlParameter：

第一次查询（分组取最大值） #

SELECT c.group AS group,
       MAX(c.timestamp) AS maxValue
FROM c
WHERE c.someValue = 'shall be included'
GROUP BY c.group

第二次查询（匹配结果） #

SELECT * FROM c WHERE ARRAY_CONTAINS(
        <第一次查询结果>,
        {
        "group": c.group,
        "maxValue": c.timestamp
        },
        false
    )

解决方案 #

可以通过子查询结合JOIN实现单条查询，同时支持参数化：

参数化单查询方案 #

SELECT c.*
FROM c
JOIN (
    SELECT g.group, MAX(g.timestamp) AS maxTimestamp
    FROM g
    WHERE g.someValue = @someValue
    GROUP BY g.group
) AS grouped
WHERE c.someValue = @someValue
  AND c.group = grouped.group
  AND c.timestamp = grouped.maxTimestamp

配合Azure SDK使用参数 #

以.NET SDK为例，代码中使用SqlParameter传入参数：

var query = new QueryDefinition(
    @"SELECT c.*
    FROM c
    JOIN (
        SELECT g.group, MAX(g.timestamp) AS maxTimestamp
        FROM g
        WHERE g.someValue = @someValue
        GROUP BY g.group
    ) AS grouped
    WHERE c.someValue = @someValue
      AND c.group = grouped.group
      AND c.timestamp = grouped.maxTimestamp"
)
.WithParameter("@someValue", "shall be included");

var resultSet = container.GetItemQueryIterator<YourDocumentType>(query);
var results = new List<YourDocumentType>();
while (resultSet.HasMoreResults)
{
    var response = await resultSet.ReadNextAsync();
    results.AddRange(response.Resource);
}

方案说明 #

1. 子查询部分先过滤出符合条件的文档，按group分组并计算每组的最大timestamp
2. 通过JOIN关联原始文档与分组结果，筛选出每组中timestamp等于最大值的文档
3. 使用@someValue参数化变量，配合Azure SDK的SqlParameter可有效防止SQL注入
4. 整个逻辑通过单条查询完成，无需多次请求Cosmos DB

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