Cosmos DB中Group By分组后过滤及排序问题求助

需求说明 #

需要针对文档中Entity.Urls数组的第一个元素进行分组，筛选出计数（cnt）大于1的分组来查找重复项，文档结构如下：

{
    "Entity": {
        "EntityId": 118788,
        "Urls": [
            {
                "Value": "http://some/url"
            }
        ]
    }
}

当前可运行的分组查询 #

以下查询可以正常执行分组统计：

SELECT c.Entity.Urls[0]["Value"] as url, count(1) as cnt
FROM c
WHERE IS_DEFINED(c.Entity.Urls) AND ARRAY_LENGTH(c.Entity.Urls) > 0
GROUP BY c.Entity.Urls[0]["Value"]

筛选cnt>1或排序的正确方式 #

直接嵌套子查询并在外层用WHERE cnt > 1会报错，正确的做法是使用HAVING子句过滤分组后的结果，同时可通过ORDER BY实现按计数降序排序：

筛选计数大于1的分组 #

SELECT c.Entity.Urls[0]["Value"] as url, count(1) as cnt
FROM c
WHERE IS_DEFINED(c.Entity.Urls) AND ARRAY_LENGTH(c.Entity.Urls) > 0
GROUP BY c.Entity.Urls[0]["Value"]
HAVING count(1) > 1

按计数降序排序 #

如果需要同时排序，只需在末尾添加ORDER BY：

SELECT c.Entity.Urls[0]["Value"] as url, count(1) as cnt
FROM c
WHERE IS_DEFINED(c.Entity.Urls) AND ARRAY_LENGTH(c.Entity.Urls) > 0
GROUP BY c.Entity.Urls[0]["Value"]
HAVING count(1) > 1
ORDER BY cnt DESC

说明 #

在Cosmos DB SQL语法中，WHERE用于过滤原始数据行，分组后的结果过滤需要使用HAVING子句——这就是嵌套查询报错的核心原因，外层WHERE无法直接引用分组统计的字段，改用HAVING即可解决问题。

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