如何在Cosmos DB中用SQL查询数组：筛选重复operationId的stockNumber

现有方法的问题解析 #

方法1：逻辑方向错误 #

你的SQL条件ARRAY_LENGTH(ARRAY(SELECT VALUE l.operationId FROM l IN c.lineItems GROUP BY l.operationId)) > 1的作用是筛选存在多个不同operationId的文档，而非存在重复operationId的文档。比如Doc-1的lineItems里只有1种operationId，分组后的数组长度为1，不会满足这个条件；而如果某个文档的lineItems有2个不同的operationId，反而会被错误选中。这和你的需求完全相反。

方法2：GROUP BY语法违规 #

Cosmos DB SQL要求SELECT子句中的非聚合字段必须出现在GROUP BY中。你按l.operationId分组，但SELECT里的c.stockNumber不在分组字段里，属于语法错误，因此抛出BadRequest。

方法3：EXISTS子查询不支持聚合操作 #

Cosmos DB的EXISTS子查询中无法直接使用GROUP BY和HAVING这类聚合逻辑，这是语法限制导致的BadRequest错误。

正确的查询写法 #

写法一：对比数组去重前后的长度 #

核心逻辑是：如果原lineItems数组的长度大于去重后的operationId数组长度，说明存在重复值。

SELECT TOP 100 c.stockNumber
FROM c
WHERE ARRAY_LENGTH(c.lineItems) > ARRAY_LENGTH(ARRAY(SELECT DISTINCT VALUE l.operationId FROM l IN c.lineItems))

写法二：使用子查询结合聚合（需通过中间数组转换） #

如果想通过聚合计数判断重复，可以先将lineItems的operationId提取为数组，再用子查询分组统计：

SELECT TOP 100 c.stockNumber
FROM c
WHERE EXISTS (
    SELECT 1
    FROM (SELECT VALUE l.operationId FROM l IN c.lineItems) AS opIds
    GROUP BY opIds
    HAVING COUNT(1) > 1
)

这两种写法都能准确筛选出lineItems数组内存在重复operationId的文档的stockNumber，针对你提供的示例数据，都会返回2002762240。

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