Azure Cosmos DB嵌套colorCounts按location分组求平均的查询方案

原始文档结构 #

[
    {
        "id": "id_1",
        "location": "location_1",
        "colorCounts": {
            "red" : 1,
            "blue": 0,
            "yellow": 1
        }
    },
    {
        "id": "id_2",
        "location": "location_1",
        "colorCounts": {
            "red" : 0,
            "blue": 0,
            "yellow": 0
        }
    }
]

需求说明 #

按location字段分组，对colorCounts嵌套对象中的每个颜色数值求平均，最终得到每个location对应的各颜色平均统计值。

问题分析 #

直接使用AVG(c.colorCounts)无法得到预期结果，因为AVG函数仅支持对单个数值字段计算平均值，不能直接作用于整个嵌套对象。

解决方案 #

针对colorCounts下的每个颜色字段单独调用AVG函数，再将结果组合成目标结构：

SELECT 
    c.location,
    {
        "red": AVG(c.colorCounts.red),
        "blue": AVG(c.colorCounts.blue),
        "yellow": AVG(c.colorCounts.yellow)
    } AS colorCounts
FROM c
GROUP BY c.location

结果验证 #

执行上述查询后，将得到符合预期的结果：

{
    "location": "location_1",
    "colorCounts": {
        "red" : 0.5,
        "blue": 0,
        "yellow": 0.5
    }
}

扩展说明 #

如果colorCounts中的颜色字段不固定（动态增减），可以考虑使用用户定义函数(UDF)或通过ARRAY函数展开嵌套对象后再做聚合计算，但固定字段场景下上述方法最直接高效。

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