azure - Select not in subquery select Cosmos DB，语法错误

Question

我最终会弄清楚，但我想把它放在那里:此查询:

SELECT distinct value e.siteid
FROM e IN c.events
where e.sensor = 'Air Temperature'

产生这个结果:

[ "07000619231FBD", "07000619236CDB", "09000619236C01", "09000619236BE3", "0A000619236BD7"]

此查询有效，正如预期的那样:

select *
from c
where c.id not in ([ "07000619231FBD", "07000619236CDB", "09000619236C01", "09000619236BE3", "0A000619236BD7"])

但是组合(即子查询)会出现语法错误:

select *
from c
where c.id not in
 (SELECT distinct value e.siteid
  FROM e IN c.events
  where e.sensor = 'Air Temperature')

我在这里缺少什么？

Answer 1

(您的第一个查询也给出了 400 BadREquest。我假设您复制错误，并且实际上在没有 IN c.events 的情况下执行了此操作，因为该查询中未定义 c .)

最有可能的问题是您正在尝试使用跨文档联接进行查询？CosmosDB 不支持联接不同文档。只允许自连接。单独的查询会起作用，因为您可以消除跨文档连接部分。

参见Subquery documentation明确地说(强调我的):

There are two main types of subqueries:

• Correlated: A subquery that references values from the outer query. The subquery is evaluated once for each row that the outer query processes.
• Non-correlated: A subquery that's independent of the outer query. It can be run on its own without relying on the outer query.

Note: Azure Cosmos DB supports only correlated subqueries.

您的初衷(单独查询)似乎是非相关查询(跨文档联接)，但您实际上编写了一个相关查询(自联接)。您可以做的，就像您已经做的那样，只需连续进行最初的 2 个查询即可。

<小时/>

如果您确实希望子查询仅检查同一文档中的气温(自连接)，那么很可能您错过了 ARRAY() 函数:

select *
from c
where c.id not in
 (ARRAY(SELECT distinct value e.siteid
  FROM e IN c.events
  where e.sensor = 'Air Temperature'))