Azure Cosmos DB SQL查询：获取去重字段组合及统计结果

需要从Azure Cosmos DB容器中查询包含type、saveToUserEmail、去重的fieldNameCombination以及统计计数count的结果，但原有SQL查询未返回正确输出。

原查询语句 #

SELECT  c.type, c.saveToUserEmail, ARRAY_CONCAT(s.fieldName, ',') AS fieldNameCombination, COUNT(1) AS count FROM c JOIN s IN c.searchCriteria.search WHERE c.type = 'CHANGEREPORT' GROUP BY c.type, c.saveToUserEmail, ARRAY_CONCAT(s.fieldName, ',')

当前错误输出 #

[
    {
        "type": "CHANGEREPORT",
        "saveToUserEmail": "anna.set@param.net",
        "count": 1
    }
]

预期输出（fieldNameCombination为唯一值） #

[
    {
        "type": "CHANGEREPORT",
        "saveToUserEmail": "anna.set@param.net",
        "fieldNameCombination": "vendorCode,changeDate",
        "count": 1
    }
]

样本容器数据 #

{
    "name": "Change Report",
    "type": "CHANGEREPORT",
    "saveToUserEmail": "anna.set@param.net",
    "isFavourite": "Y",
    "searchCriteria": {
        "fields": [
            "vendorCode",
            "poNumber",
            "itemNumber",
            "itemText"
        ],
        "search": [
            {
                "fieldName": "vendorCode",
                "operator": "=",
                "fieldValue": [
                    "IR"
                ]
            },
            {
                "fieldName": "changeDate",
                "operator": ">=",
                "fieldValue": "2021-01-01"
            },
            {
                "fieldName": "changeDate",
                "operator": "<=",
                "fieldValue": "2021-11-02"
            }
        ],
        "filter": [],
        "count": 1000,
        "offset": "0"
    },
    "id": "f00001f8f-2dec-41e0-b6b9-728eb6d8e123",
    "saveCriteriaID": "f8577f8f-2dec-41e0-b6b9-728eb6d8e847",
    "_rid": "F15IANpVIe0BAAAAAAAAAA==",
    "_self": "dbs/F15IAA==/colls/F15IANpVIe0=/docs/F15IANpVIe0BAAAAAAAAAA==/",
    "_etag": "\"44007a88-0000-1800-0000-6180c01a0000\"",
    "_attachments": "attachments/",
    "_ts": 1635827738
}

解决方案 #

原查询存在两个核心问题：

1. 使用JOIN拆分search数组后，会将每个数组元素单独处理，无法直接合并去重；
2. ARRAY_CONCAT用于合并数组，不能实现字符串拼接，且未对fieldName去重。

正确的SQL查询如下：

SELECT 
    doc.type, 
    doc.saveToUserEmail, 
    doc.fieldNameCombination, 
    COUNT(1) AS count
FROM (
    SELECT 
        c.type, 
        c.saveToUserEmail, 
        STRING_JOIN(',', ARRAY_DISTINCT(ARRAY(SELECT s.fieldName FROM s IN c.searchCriteria.search))) AS fieldNameCombination
    FROM c
    WHERE c.type = 'CHANGEREPORT'
) doc
GROUP BY doc.type, doc.saveToUserEmail, doc.fieldNameCombination

逻辑说明 #

1. 子查询中：
   • 使用ARRAY(SELECT s.fieldName FROM s IN c.searchCriteria.search)提取当前文档所有search项的fieldName，生成数组；
   • ARRAY_DISTINCT对数组去重，得到唯一的fieldName列表；
   • STRING_JOIN将去重后的数组拼接为逗号分隔的字符串fieldNameCombination。
2. 外层查询按type、saveToUserEmail和fieldNameCombination分组，统计每组的文档数量。

执行该查询后，将得到符合预期的输出结果。

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