在Cosmos DB中按多属性分组并聚合唯一trim值

需求：对Cosmos DB集合（查询中别名为c）按照year、model_name、brand三个属性分组，将每组中的trim字段聚合为仅包含唯一值的列表。

集合数据示例 #

[
    {
        "trim": "8375",
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    },
    {
        "trim": "9854",
        "year": "2022",
        "model_name": "NX Hybrid",
        "brand": "LEXUS"
    },
    {
        "trim": "8361",
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    },
    {
        "trim": "8382",
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    },
    {
        "trim": "9854",
        "year": "2022",
        "model_name": "NX Hybrid",
        "brand": "LEXUS"
    },
    {
        "trim": "8386",
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    },
    {
        "trim": "9764",
        "year": "2022",
        "model_name": "NX Hybrid",
        "brand": "LEXUS"
    },
    {
        "trim": "8361",
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    },
    {
        "trim": "8261",
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    },
    {
        "trim": "8376",
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    },
    {
        "trim": "8361",
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    }
]

期望分组结果 #

[
    {
        "trim": ["8375", "8361", "8382", "8386", "8261", "8376"],
        "year": "2022",
        "model_name": "Tundra",
        "brand": "TOYOTA"
    },
    {
        "trim": ["9854", "9764"],
        "year": "2022",
        "model_name": "NX Hybrid",
        "brand": "LEXUS"
    }
]

实现查询语句 #

使用Cosmos DB SQL的分组聚合函数即可实现需求，查询语句如下：

SELECT 
    c.year,
    c.model_name,
    c.brand,
    ARRAY_DISTINCT(ARRAY_AGG(c.trim)) AS trim
FROM c
GROUP BY c.year, c.model_name, c.brand

逻辑说明 #

• GROUP BY c.year, c.model_name, c.brand：按照指定的三个字段对文档进行分组
• ARRAY_AGG(c.trim)：将每组内所有的trim值收集为一个数组
• ARRAY_DISTINCT(...)：对收集到的数组进行去重，得到仅包含唯一值的trim列表

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