Povratne informacije Uredi

WHERE clause in Azure Cosmos DB

  • Članak
  • 2 min. za čitanje

APPLIES TO: SQL API

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. A JSON item must evaluate the specified conditions to true to be considered for the result. 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

In order for the document to be returned an expression specified as filter condition must evaluate to true. Only Boolean value true will satisfy the condition, any other value: undefined, null, false, Number, Array, or Object will not satisfy the condition.

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

The following query requests items that contain an id property whose value is 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"
      }
    }]

Scalar expressions in the WHERE clause

The previous example showed a simple equality query. The SQL API also supports various scalar expressions. The most commonly used are binary and unary expressions. 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, NOT
Comparison =, !=, <, >, <=, >=, <>
String || (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

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. For example, SELECT * FROM Families f WHERE f.isRegistered returns the JSON item containing the property isRegistered with value equal to true. Any other value, such as false, null, Undefined, <number>, <string>, <object>, or <array>, excludes the item from the result. Additionally, you can use the IS_DEFINED type checking function to query based on the presence or absence of a given JSON property. For instance, SELECT * FROM Families f WHERE NOT IS_DEFINED(f.isRegistered) returns any JSON item that does not have a value for isRegistered.

Next steps