如何在CosmosDB中使用Join操作/子查询查询并删除CabId重复项

错误原因 #

你写的SQL用了传统关系型数据库的JOIN ON关联语法，这类语法在Cosmos DB的SQL查询中不被支持：Cosmos DB的JOIN仅支持同一个文档内部的嵌套数组展开，不支持跨文档的关联查询，也不支持将子查询结果作为JOIN的数据源，因此触发了语法报错。

修正后的查询语句 #

如果你需要获取指定分区下所有CabId重复的完整文档条目，可以直接用IN子查询实现，写法如下：

SELECT *
FROM c
WHERE c.PartitionKey = "29997810-1238691972021-10-25"
AND c.CabId IN (
    SELECT d.CabId
    FROM d
    WHERE d.PartitionKey = "29997810-1238691972021-10-25"
    GROUP BY d.CabId
    HAVING COUNT(d.CabId) > 1
)

这个语句会先通过子查询查出该分区下所有出现次数大于1的CabId列表，再查询出这些CabId对应的所有完整文档。

重复条目删除注意事项 #

• Cosmos DB删除文档需要对应文档的唯一id字段值和分区键值，你可以将上面的查询调整为返回id和PartitionKey，再通过批量接口或者SDK循环执行删除操作。
• 如果你需要保留每个CabId的1条有效记录、只删除多余重复项，可以将查询结果按CabId分组，每个组内保留1条记录后再删除剩余条目。
• 如果需要处理全容器的重复数据，建议按分区分批执行查询和删除，避免全表扫描消耗过高RU影响正常业务；如果重复CabId的数量非常多，建议先单独执行子查询拿到重复CabId列表，再分批放到IN条件中查询，避免查询语句过长。

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