如何编写Cosmos DB查询按GroupId聚合Location和Status数组

需求说明 #

给定Cosmos DB集合中的示例数据：

[
    {
        "id": "00000000-0000-0000-454c-4b74472b01d8",   
        "GroupId": 1,
        "Location": "London",
        "Status": "Ok"
    },
    {
        "id": "d129adeb-d1bf-4a89-afe3-93e3f60589fb",   
        "GroupId": 1,
        "Location": "Liverpool",
        "Status": "Ok"
    },
    {
        "id": "85ecf875-0e32-40b5-823a-a2545694f9b6",   
        "GroupId": 2,
        "Location": "Manchester",
        "Status": "Nok"
    }
]

需要实现：指定GroupId后，返回该分组下Location和Status的所有唯一值数组，例如指定GroupId=1时返回：

{
    "Location": ["London", "Liverpool"],
    "Status": ["Ok"]
}

可行查询方案 #

方案1：带DISTINCT的子查询数组（单分组查询） #

这种写法直接针对指定分组，生成去重后的字段数组：

-- 替换@groupId为具体分组ID，如1或2
SELECT 
    ARRAY(SELECT DISTINCT VALUE c.Location FROM c WHERE c.GroupId = @groupId) AS Location,
    ARRAY(SELECT DISTINCT VALUE c.Status FROM c WHERE c.GroupId = @groupId) AS Status

方案2：GROUP BY + ARRAY_AGG（支持单/多分组查询） #

利用Cosmos DB的ARRAY_AGG聚合函数结合DISTINCT，可以实现分组聚合：

-- 查询单个分组
SELECT 
    ARRAY_AGG(DISTINCT c.Location) AS Location,
    ARRAY_AGG(DISTINCT c.Status) AS Status
FROM c
WHERE c.GroupId = 1
GROUP BY c.GroupId

-- 查询所有分组的结果（每个分组一行数据）
SELECT 
    c.GroupId,
    ARRAY_AGG(DISTINCT c.Location) AS Location,
    ARRAY_AGG(DISTINCT c.Status) AS Status
FROM c
GROUP BY c.GroupId

原查询写法的问题分析 #

• 第一种尝试：外层FROM c WHERE c.GroupId=1会遍历每个符合条件的文档，导致子查询结果被重复返回，最终得到多条重复的聚合结果，而非单一对象。
• 第二种尝试：子查询未添加GroupId筛选，且嵌套数组的写法错误，生成的结构不符合需求，同时没有实现去重。

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