Azure Cosmos DB 中的 WHERE 子句WHERE clause in Azure Cosmos DB

可选的 WHERE 子句 (WHERE <filter_condition>) 指定条件,查询只会将满足这些条件的源 JSON 项包含在结果中。The optional WHERE clause (WHERE <filter_condition>) specifies condition(s) that the source JSON items must satisfy for the query to include them in results. JSON 项必须将指定的条件评估为 true 才被视作结果。A JSON item must evaluate the specified conditions to true to be considered for the result. 索引层使用 WHERE 子句来确定可以作为结果的一部分的源项的最小子集。The index layer uses the WHERE clause to determine the smallest subset of source items that can be part of the result.

语法Syntax

WHERE <filter_condition>  
<filter_condition> ::= <scalar_expression>  

参数Arguments

  • <filter_condition>

    指定需要满足什么条件才会返回文档。Specifies the condition to be met for the documents to be returned.

  • <scalar_expression>

    表示待计算值的表达式。Expression representing the value to be computed. 有关详细信息,请参阅标量表达式See Scalar expressions for details.

备注Remarks

指定为筛选条件的表达式的求值结果必须为 true,才会返回文档。In order for the document to be returned an expression specified as filter condition must evaluate to true. 只有布尔值 true 会满足条件,其他任何值(undefined、null、false、数字、数组或对象)都不会满足条件。Only Boolean value true will satisfy the condition, any other value: undefined, null, false, Number, Array, or Object will not satisfy the condition.

如果将分区键作为等式筛选器的一部分包含在 WHERE 子句中,则查询将仅自动筛选出相关分区。If you include your partition key in the WHERE clause as part of an equality filter, your query will automatically filter to only the relevant partitions.

示例Examples

以下查询请求包含值为 AndersenFamilyid 属性的项。The following query requests items that contain an id property whose value is AndersenFamily. 它会排除任何不带 id 属性或值与 AndersenFamily 不匹配的项。It excludes any item that does not have an id property or whose value doesn't match AndersenFamily.

SELECT f.address
FROM Families f
WHERE f.id = "AndersenFamily"

结果有:The results are:

[{
  "address": {
    "state": "WA",
    "county": "King",
    "city": "Seattle"
  }
}]

WHERE 子句中的标量表达式Scalar expressions in the WHERE clause

上面的示例演示了一个简单的等式查询。The previous example showed a simple equality query. SQL API 还支持各种标量表达式The SQL API also supports various scalar expressions. 最常使用的是二进制和一元表达式。The most commonly used are binary and unary expressions. 来自源 JSON 对象的属性引用也是有效的表达式。Property references from the source JSON object are also valid expressions.

可以使用以下受支持的二元运算符:You can use the following supported binary operators:

运算符类型Operator type Values
算术Arithmetic +,-,*,/,%+,-,*,/,%
Bitwise |、&、^、<<、>>、>>>(补零右移)|, &, ^, <<, >>, >>> (zero-fill right shift)
逻辑Logical AND、OR、NOTAND, OR, NOT
比较Comparison =、!=、<、>、<=、>=、<>=, !=, <, >, <=, >=, <>
StringString ||(连接)|| (concatenate)

以下查询使用二元运算符:The following queries use binary operators:

SELECT *
FROM Families.children[0] c
WHERE c.grade % 2 = 1     -- matching grades == 5, 1

SELECT *
FROM Families.children[0] c
WHERE c.grade ^ 4 = 1    -- matching grades == 5

SELECT *
FROM Families.children[0] c
WHERE c.grade >= 5    -- matching grades == 5

还可以在查询中使用一元运算符 +、-、~ 和 NOT,如以下示例所示:You can also use the unary operators +,-, ~, and NOT in queries, as shown in the following examples:

SELECT *
FROM Families.children[0] c
WHERE NOT(c.grade = 5)  -- matching grades == 1

SELECT *
FROM Families.children[0] c
WHERE (-c.grade = -5)  -- matching grades == 5

还可以在查询中使用属性引用。You can also use property references in queries. 例如,SELECT * FROM Families f WHERE f.isRegistered 返回包含值等于 trueisRegistered 属性的 JSON 项。For example, SELECT * FROM Families f WHERE f.isRegistered returns the JSON item containing the property isRegistered with value equal to true. 任何其他值(例如falsenullUndefined<number><string><object><array>)会从结果中排除该项。Any other value, such as false, null, Undefined, <number>, <string>, <object>, or <array>, excludes the item from the result.

后续步骤Next steps