Azure Cosmos DB 中的关键字Keywords in Azure Cosmos DB

本文详细介绍可在 Azure Cosmos DB SQL 查询中使用的关键字。This article details keywords which may be used in Azure Cosmos DB SQL queries.

BETWEENBETWEEN

可以使用 BETWEEN 关键字来表达针对字符串或数值范围的查询。You can use the BETWEEN keyword to express queries against ranges of string or numerical values. 例如,以下查询返回其中第一个孩子的年级为 1-5(含)的所有项。For example, the following query returns all items in which the first child's grade is 1-5, inclusive.

    SELECT *
    FROM Families.children[0] c
    WHERE c.grade BETWEEN 1 AND 5

还可以在 SELECT 子句中使用 BETWEEN 关键字,如以下示例所示。You can also use the BETWEEN keyword in the SELECT clause, as in the following example.

    SELECT (c.grade BETWEEN 0 AND 10)
    FROM Families.children[0] c

与 ANSI SQL 不同,在 SQL API 中,可以针对混合类型的属性表达范围查询。In SQL API, unlike ANSI SQL, you can express range queries against properties of mixed types. 例如,在某些项中,grade 可能是类似于 5 的数字;而在其他一些项中,它可能是类似于 grade4 的字符串。For example, grade might be a number like 5 in some items and a string like grade4 in others. 在这些情况下(与在 JavaScript 中一样),两个不同类型之间的比较会生成 Undefined,因此会跳过该项。In these cases, as in JavaScript, the comparison between the two different types results in Undefined, so the item is skipped.

Tip

为了更快地执行查询,请创建一个索引策略,该策略对 BETWEEN 子句筛选的任何数值属性或路径使用范围索引类型。For faster query execution times, create an indexing policy that uses a range index type against any numeric properties or paths that the BETWEEN clause filters.

DISTINCTDISTINCT

DISTINCT 关键字可消除查询投影中的重复项。The DISTINCT keyword eliminates duplicates in the query's projection.

在此示例中,查询将投影每个姓氏的值:In this example, the query projects values for each last name:

SELECT DISTINCT VALUE f.lastName
FROM Families f

结果有:The results are:

[
    "Andersen"
]

也可以投影唯一对象。You can also project unique objects. 在本例中,两个文档中的一个文档不存在 lastName 对象,因此查询将返回一个空对象。In this case, the lastName field does not exist in one of the two documents, so the query returns an empty object.

SELECT DISTINCT f.lastName
FROM Families f

结果有:The results are:

[
    {
        "lastName": "Andersen"
    },
    {}
]

还可以在子查询内的投影中使用 DISTINCT:DISTINCT can also be used in the projection within a subquery:

SELECT f.id, ARRAY(SELECT DISTINCT VALUE c.givenName FROM c IN f.children) as ChildNames
FROM f

此查询投影包含每个孩子的 givenName 的数组,并删除了重复项。This query projects an array which contains each child's givenName with duplicates removed. 此数组的别名为 ChildNames,并在外部查询中投影。This array is aliased as ChildNames and projected in the outer query.

结果有:The results are:

[
    {
        "id": "AndersenFamily",
        "ChildNames": []
    },
    {
        "id": "WakefieldFamily",
        "ChildNames": [
            "Jesse",
            "Lisa"
        ]
    }
]

不支持查询包含使用 DISTINCT 的聚合系统函数和子查询。Queries with an aggregate system function and a subquery with DISTINCT are not supported. 例如,不支持以下查询:For example, the following query is not supported:

SELECT COUNT(1) FROM (SELECT DISTINCT f.lastName FROM f)

ININ

使用 IN 关键字可以检查指定的值是否与列表中的任一值匹配。Use the IN keyword to check whether a specified value matches any value in a list. 例如,以下查询返回 idWakefieldFamilyAndersenFamily 的所有家庭项。For example, the following query returns all family items where the id is WakefieldFamily or AndersenFamily.

    SELECT *
    FROM Families
    WHERE Families.id IN ('AndersenFamily', 'WakefieldFamily')

以下示例返回状态为任何指定值的所有项:The following example returns all items where the state is any of the specified values:

    SELECT *
    FROM Families
    WHERE Families.address.state IN ("NY", "WA", "CA", "PA", "OH", "OR", "MI", "WI", "MN", "FL")

SQL API 支持循环访问 JSON 数组,它可以通过 FROM 源中的 IN 关键字添加一个新的构造。The SQL API provides support for iterating over JSON arrays, with a new construct added via the in keyword in the FROM source.

如果在 IN 筛选器中包含分区键,则查询会自动地仅筛选出相关分区。If you include your partition key in the IN filter, your query will automatically filter to only the relevant partitions.

TOPTOP

TOP 关键字以未定义的顺序返回前 N 个查询结果。The TOP keyword returns the first N number of query results in an undefined order. 最佳做法是将 TOP 与 ORDER BY 子句配合使用,将结果限制为前 N 个有序值。As a best practice, use TOP with the ORDER BY clause to limit results to the first N number of ordered values. 要预见性地指示哪些行受到 TOP 的影响,只能结合使用这两个子句。Combining these two clauses is the only way to predictably indicate which rows TOP affects.

可以结合一个常量值使用 TOP(如以下示例中所示),或者在参数化查询中结合一个变量值使用 TOP。You can use TOP with a constant value, as in the following example, or with a variable value using parameterized queries.

    SELECT TOP 1 *
    FROM Families f

结果有:The results are:

    [{
        "id": "AndersenFamily",
        "lastName": "Andersen",
        "parents": [
           { "firstName": "Thomas" },
           { "firstName": "Mary Kay"}
        ],
        "children": [
           {
               "firstName": "Henriette Thaulow", "gender": "female", "grade": 5,
               "pets": [{ "givenName": "Fluffy" }]
           }
        ],
        "address": { "state": "WA", "county": "King", "city": "Seattle" },
        "creationDate": 1431620472,
        "isRegistered": true
    }]

后续步骤Next steps