Cosmos DB SQL API如何查询跨文档项的重复属性值

问题背景 #

需要在Cosmos DB SQL API中实现：校验容器内文档的resetPassword[].oneTimeCode属性值是否存在跨文档重复。
示例文档结构：

{
    "id": "1",
    "resetPassword": [
        {
            "oneTimeCode": "hug3jh",
            "type": "code"
        }
    ],
    "expirationDateTime": 1651234557057
}

初始尝试的查询触发「无法解析oneTimeCode」报错，期望实现类似标准SQL中GROUP BY + HAVING COUNT(*) >1的重复值筛查效果。

报错核心原因 #

• Cosmos DB SQL API要求查询源必须指定别名，直接引用嵌套路径、不带别名访问属性会导致解析失败
• resetPassword是数组类型字段，未做数组展开时无法直接访问数组内部元素的oneTimeCode属性
• 带特殊字符（如横杠）的容器名不能直接作为查询根引用，必须显式指定别名

正确查询写法 #

第一步：查询所有重复的oneTimeCode值 #

通过JOIN语法展开嵌套数组，再做分组统计，语句如下：

SELECT
    resetItem.oneTimeCode AS repeatedCode,
    COUNT(1) AS appearCount
FROM c IN password-reset
JOIN resetItem IN c.resetPassword
GROUP BY resetItem.oneTimeCode
HAVING COUNT(1) > 1

语句说明：

• c IN password-reset：给名为password-reset的容器指定别名c，适配带特殊字符的容器名
• JOIN resetItem IN c.resetPassword：Cosmos DB中JOIN用于展开文档内的嵌套数组，将数组中每个元素映射为resetItem对象，展开后即可正常访问内部的oneTimeCode属性
• 分组后筛选出现次数大于1的值，即为跨文档重复的验证码

第二步：查询重复值关联的所有文档ID #

如果需要定位重复值所属的具体文档，可以拿第一步查出的重复code列表做匹配查询：

SELECT
    c.id,
    resetItem.oneTimeCode
FROM c IN password-reset
JOIN resetItem IN c.resetPassword
WHERE ARRAY_CONTAINS(
    -- 替换为第一步查询得到的重复oneTimeCode数组
    ['hug3jh', 'abc123'],
    resetItem.oneTimeCode
)

优化注意事项 #

• 如果存在resetPassword为空数组、数组元素缺失oneTimeCode字段的场景，可以在JOIN后增加WHERE IS_DEFINED(resetItem.oneTimeCode)过滤无效数据，避免统计异常
• 如果数据量较大，建议为resetPassword.oneTimeCode配置对应的索引策略，降低查询的RU消耗

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