Azure Cosmos DB 中的 ORDER BY 子句ORDER BY clause in Azure Cosmos DB

可选的 ORDER BY 子句指定查询返回的结果的排序顺序。The optional ORDER BY clause specifies the sorting order for results returned by the query.

语法Syntax

ORDER BY <sort_specification>  
<sort_specification> ::= <sort_expression> [, <sort_expression>]  
<sort_expression> ::= {<scalar_expression> [ASC | DESC]} [ ,...n ]  

参数Arguments

  • <sort_specification>

    指定对查询结果集进行排序时要依据的属性或表达式。Specifies a property or expression on which to sort the query result set. 可将排序列指定为名称或属性别名。A sort column can be specified as a name or property alias.

    可以指定多个属性。Multiple properties can be specified. 属性名称必须唯一。Property names must be unique. ORDER BY 子句中排序属性的顺序定义了排序的结果集的组织方式。The sequence of the sort properties in the ORDER BY clause defines the organization of the sorted result set. 也就是说,结果集首先按第一个属性排序,然后该有序列表按第二个属性排序,依此类推。That is, the result set is sorted by the first property and then that ordered list is sorted by the second property, and so on.

    ORDER BY 子句中引用的属性名称必须与所选列表中的某个属性或者与 FROM 子句中指定的集合中定义的某个属性相对应,且不存在任何多义性。The property names referenced in the ORDER BY clause must correspond to either a property in the select list or to a property defined in the collection specified in the FROM clause without any ambiguities.

  • <sort_expression>

    指定一个或多个属性或表达式用作排序查询结果集的依据。Specifies one or more properties or expressions on which to sort the query result set.

  • <scalar_expression>

    有关详细信息,请参阅标量表达式部分。See the Scalar expressions section for details.

  • ASC | DESC

    指定应当按升序或降序对指定列中的值进行排序。Specifies that the values in the specified column should be sorted in ascending or descending order. ASC 将按照从最低值到最高值的顺序排序。ASC sorts from the lowest value to highest value. DESC 将按照从最高值到最低值的顺序排序。DESC sorts from highest value to lowest value. ASC 是默认排序顺序。ASC is the default sort order. Null 值被视为最低的可能值。Null values are treated as the lowest possible values.

备注Remarks

ORDER BY 子句要求索引策略包含所要排序的字段的索引。The ORDER BY clause requires that the indexing policy include an index for the fields being sorted. Azure Cosmos DB 查询运行时支持根据属性名称排序,而不支持根据计算的属性排序。The Azure Cosmos DB query runtime supports sorting against a property name and not against computed properties. Azure Cosmos DB 支持多个 ORDER BY 属性。Azure Cosmos DB supports multiple ORDER BY properties. 若要运行包含多个 ORDER BY 属性的查询,应在所要排序的字段中定义组合索引In order to run a query with multiple ORDER BY properties, you should define a composite index on the fields being sorted.

Note

如果要排序的属性对于某些文档而言可能未定义,并且你希望在 ORDER BY 查询中检索这些属性,则必须在索引中显式包含此路径。If the properties being sorted might be undefined for some documents and you want to retrieve them in an ORDER BY query, you must explicitly include this path in the index. 默认索引策略不允许检索未定义排序属性的文档。The default indexing policy won't allow for the retrieval of the documents where the sort property is undefined. 查看针对缺少一些字段的文档的示例查询Review example queries on documents with some missing fields.

示例Examples

例如,以下查询按居住城市名称的升序检索家庭:For example, here's a query that retrieves families in ascending order of the resident city's name:

    SELECT f.id, f.address.city
    FROM Families f
    ORDER BY f.address.city

结果有:The results are:

    [
      {
        "id": "WakefieldFamily",
        "city": "NY"
      },
      {
        "id": "AndersenFamily",
        "city": "Seattle"
      }
    ]

以下查询按项的创建日期检索家庭 idThe following query retrieves family ids in order of their item creation date. creationDate 是一个数字,表示纪元时间,或者自 1970 年 1 月 1 日开始消逝的时间(以秒为单位)。Item creationDate is a number representing the epoch time, or elapsed time since Jan. 1, 1970 in seconds.

    SELECT f.id, f.creationDate
    FROM Families f
    ORDER BY f.creationDate DESC

结果有:The results are:

    [
      {
        "id": "WakefieldFamily",
        "creationDate": 1431620462
      },
      {
        "id": "AndersenFamily",
        "creationDate": 1431620472
      }
    ]

此外,可按多个属性排序。Additionally, you can order by multiple properties. 按多个属性排序的查询需要组合索引A query that orders by multiple properties requires a composite index. 请考虑下列查询:Consider the following query:

    SELECT f.id, f.creationDate
    FROM Families f
    ORDER BY f.address.city ASC, f.creationDate DESC

此查询按城市名称的升序检索家庭 idThis query retrieves the family id in ascending order of the city name. 如果多个项包含同一个城市名称,该查询将按 creationDate 的降序排序。If multiple items have the same city name, the query will order by the creationDate in descending order.

缺少字段的文档Documents with missing fields

针对采用默认索引策略的容器运行包含 ORDER BY 的查询不会返回未定义排序属性的文档。Queries with ORDER BY that are run against containers with the default indexing policy will not return documents where the sort property is undefined. 若要包含未定义排序属性的文档,应在索引策略中显式包含此属性。If you would like to include documents where the sort property is undefined, you should explicitly include this property in the indexing policy.

例如,以下容器的索引策略未显式包含除 "/*" 以外的其他任何路径:For example, here's a container with an indexing policy that does not explicitly include any paths besides "/*":

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": []
}

如果运行一个在 Order By 子句中包含 lastName 的查询,则结果只包括定义了 lastName 属性的文档。If you run a query that includes lastName in the Order By clause, the results will only include documents that have a lastName property defined. 我们尚未为 lastName 定义显式包含路径,因此查询结果中不会显示任何没有 lastName 的文档。We have not defined an explicit included path for lastName so any documents without a lastName will not appear in the query results.

以下查询按 lastName 对两个文档进行排序,对于其中的一个文档,尚未定义 lastNameHere is a query that sorts by lastName on two documents, one of which does not have a lastName defined:

    SELECT f.id, f.lastName
    FROM Families f
    ORDER BY f.lastName

结果仅包括定义了 lastName 的文档:The results only include the document that has a defined lastName:

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

如果我们将该容器的索引策略更新为显式包含 lastName 的路径,则查询结果中会包括具有未定义排序属性的文档。If we update the container's indexing policy to explicitly include a path for lastName, we will include documents with an undefined sort property in the query results. 必须显式定义路径才能生成此标量值(而不是生成其他值)。You must explicitly define the path to lead to this scalar value (and not beyond it). 应在索引策略的路径定义中使用 ? 字符,确保为 lastName 属性显式编制索引,且不会包含除此之外的其他嵌套路径。You should use the ? character in your path definition in the indexing policy to ensure that you explicitly index the property lastName and no additional nested paths beyond it. 如果 Order By 查询使用组合索引,则查询结果中始终包括具有未定义排序属性的文档。If your Order By query uses a composite index, the results will always include documents with an undefined sort property in the query results.

使用以下示例索引策略可以在查询结果中显示具有未定义的 lastName 的文档:Here is a sample indexing policy which allows you to have documents with an undefined lastName appear in the query results:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/lastName/?"
        },
        {
            "path": "/*"
        }
    ],
    "excludedPaths": []
}

如果再次运行同一查询,则缺少 lastName 的文档会显示在查询结果中的最前面:If you run the same query again, documents that are missing lastName appear first in the query results:

    SELECT f.id, f.lastName
    FROM Families f
    ORDER BY f.lastName

结果有:The results are:

[
    {
        "id": "WakefieldFamily"
    },
    {
        "id": "AndersenFamily",
        "lastName": "Andersen"
    }
]

如果将排序顺序修改为 DESC,则缺少 lastName 的文档会显示在查询结果中的最后面:If you modify the sort order to DESC, documents that are missing lastName appear last in the query results:

    SELECT f.id, f.lastName
    FROM Families f
    ORDER BY f.lastName DESC

结果有:The results are:

[
    {
        "id": "AndersenFamily",
        "lastName": "Andersen"
    },
    {
        "id": "WakefieldFamily"
    }
]

Note

仅 .NET SDK 3.4.0 或更高版本支持混合类型的 ORDER BY。Only the .NET SDK version 3.4.0 or later supports ORDER BY with mixed types. 因此,如果要按未定义值和已定义值的组合进行排序,则应使用此版本(或更高版本)。Therefore, if you want to sort by a combination of undefined and defined values, you should use this version (or later).

你无法控制不同类型在结果中的显示顺序。You can't control the order that different types appear in the results. 在上面的示例中,我们演示了如何在字符串值之前对未定义值进行排序。In the above example, we showed how undefined values were sorted before string values. 例如,如果想更好地控制未定义值的排序顺序,则可为任何未定义属性分配字符串值“aaaaaaaaa”或“zzzzzzzz”,以确保它们是第一个或最后一个。If instead, for example, you wanted more control over the sort order of undefined values, you could assign any undefined properties a string value of "aaaaaaaaa" or "zzzzzzzz" to ensure they were either first or last.

后续步骤Next steps