azure - CosmosDb 查询返回数组

Question

我的收藏中有以下数据

{
    "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"
}

我需要构建一个查询来按Group获取所有可能的值进行过滤。假设对于 "GroupId": 1 我需要这样的结果

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

对于“GroupId”:2响应:

{
    "Location": [
        "Manchester",
    ],
    "Status": [
        "Nok"
    ]
}

你能帮我建立这样的查询吗？我不知道 CosmosDb 是否可以。到目前为止我已经尝试过类似的方法，但它不起作用

select  
(
    select VALUE c.Location
    FROM c
    WHERE c.GroupId = 1
    GROUP BY c.Location
) as Location,
(
    select VALUE c.Status
    FROM c
    WHERE c.GroupId = 1
    GROUP BY c.Status
) as Status
from c
WHERE c.GroupId = 1

还有这个

select 
[
    (SELECT VALUE [c.Location] from c)
] as Location,
[
    (SELECT VALUE [c.Status] from c)
] as Status
from c
where c.GroupId = 1

请帮忙或建议如何解决这个问题。预先感谢您。

Answer 1

按照数据建模方式不可能做到这一点。

与 ARRAY expression您可以在文档中的数组子查询中执行此操作。但当数据跨越文档时则不然，就像这里的情况一样。