Cosmos DB中DISTINCT未按WHERE条件过滤的问题

问题根源 #

你遇到的问题并非DISTINCT不遵循WHERE规则，而是JOIN与SELECT的逻辑搭配错误：

1. 使用JOIN t IN c.lineItems时，Cosmos DB会将每个lineItems元素展开为单独的行记录
2. WHERE子句确实过滤出了t.operationId = 1400742的行，但你SELECT的是原文档的完整c.lineItems数组，而非过滤后的单个t元素
3. DISTINCT在这里是对返回的整个文档对象去重，但每个文档的id唯一，最终只会返回一次完整的原文档，自然包含所有lineItems元素

正确解决方案 #

要返回仅包含符合条件的lineItems元素的文档，需要直接过滤数组，而非依赖JOIN展开后再返回原数组。推荐使用ARRAY_FILTER函数实现：

SELECT c.id, c.stockNumber, c.createdDateTime, 
       ARRAY_FILTER(c.lineItems, t => t.operationId = 1400742) AS lineItems
FROM c
WHERE c.stockNumber IN ('123')
-- 确保只返回至少有一个符合条件lineItem的文档
AND ARRAY_LENGTH(ARRAY_FILTER(c.lineItems, t => t.operationId = 1400742)) > 0

逻辑说明 #

• ARRAY_FILTER遍历c.lineItems数组，仅保留operationId = 1400742的元素，生成新数组作为lineItems字段返回
• WHERE子句中的ARRAY_LENGTH判断确保只返回包含符合条件元素的文档，避免返回空数组的文档

如果坚持使用JOIN方式，也可以通过子查询构造过滤后的数组，但效率略低：

SELECT DISTINCT c.id, c.stockNumber, c.createdDateTime, 
       ARRAY(SELECT VALUE t FROM t IN c.lineItems WHERE t.operationId = 1400742) AS lineItems
FROM c
JOIN t IN c.lineItems
WHERE c.stockNumber IN ('123')
AND t.operationId = 1400742

执行上述修正后的查询，就能得到你预期的仅包含目标lineItems元素的结果。

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