如何在指定时间范围内检测CosmosDB文档中重复的航班对象

问题根源 #

之前生成的查询出现假阳性，核心原因是没排除数组内同一索引的航班自匹配，导致每个航班都会和自身配对，误判为重复。此外，Cosmos SQL对部分SQL语法（如HAVING的部分用法）支持有限，需调整写法适配。

可用查询语句（无假阳性） #

方案一：双JOIN+聚合去重 #

SELECT 
    grouped.Id,
    grouped.Name,
    STRINGTOOBJECT(grouped.flightKey) AS DuplicateFlight
FROM (
    SELECT 
        f.Id,
        f.Name,
        STRINGIFY(l1) AS flightKey,
        COUNT(1) AS occurrence
    FROM f
    JOIN l1 IN f.Flights WITH INDEX l1_Index
    JOIN l2 IN f.Flights WITH INDEX l2_Index
    WHERE 
        -- 替换为实际年份，确保时间格式为ISO 8601
        f.CreatedAt >= '2025-02-10T00:00:00Z' 
        AND f.CreatedAt <= '2025-02-11T23:59:59Z'
        -- 排除同一索引的自匹配，彻底解决假阳性
        AND l1_Index != l2_Index
        -- 用STRINGIFY判断航班对象是否完全一致
        AND STRINGIFY(l1) = STRINGIFY(l2)
    GROUP BY f.Id, f.Name, STRINGIFY(l1)
    -- 只保留出现次数超过1的重复航班
    HAVING COUNT(1) > 1
) AS grouped

方案二：单JOIN+子查询统计（性能更优） #

如果Flights数组数据量较大，双JOIN会产生笛卡尔积影响性能，可改用以下写法：

SELECT 
    f.Id,
    f.Name,
    l1 AS DuplicateFlight
FROM f
JOIN l1 IN f.Flights
WHERE 
    f.CreatedAt >= '2025-02-10T00:00:00Z' 
    AND f.CreatedAt <= '2025-02-11T23:59:59Z'
    -- 子查询统计当前航班在数组中的出现次数
    AND (
        SELECT VALUE COUNT(1)
        FROM l2 IN f.Flights
        WHERE STRINGIFY(l2) = STRINGIFY(l1)
    ) > 1
-- 去重，避免同一重复航班多次返回
GROUP BY f.Id, f.Name, STRINGIFY(l1), l1

查询关键说明 #

1. 时间筛选：必须使用ISO 8601格式的时间字符串（带时区标识Z），确保和CosmosDB存储的CreatedAt格式匹配。
2. 避免自匹配：通过WITH INDEX获取航班在数组中的索引，添加l1_Index != l2_Index条件，排除同一航班对象和自身配对的情况。
3. 重复判断：用STRINGIFY()把航班对象转为JSON字符串，通过字符串相等判断两个航班是否完全一致（比直接比较对象更稳定，避免隐式转换问题）。
4. 去重处理：通过GROUP BY或子查询统计次数，确保每个重复航班只返回一次，避免冗余结果。

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