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

每个查询按 ANSI SQL 标准由 SELECT 子句和可选的 FROMWHERE 子句组成。Every query consists of a SELECT clause and optional FROM and WHERE clauses, per ANSI SQL standards. 通常,将会枚举 FROM 子句中的源,WHERE 子句对该源应用一个筛选器,以检索 JSON 项的子集。Typically, the source in the FROM clause is enumerated, and the WHERE clause applies a filter on the source to retrieve a subset of JSON items. 然后,SELECT 子句在 select 列表中投影请求的 JSON 值。The SELECT clause then projects the requested JSON values in the select list.

语法Syntax

SELECT <select_specification>  

<select_specification> ::=   
      '*'   
      | [DISTINCT] <object_property_list>   
      | [DISTINCT] VALUE <scalar_expression> [[ AS ] value_alias]  

<object_property_list> ::=   
{ <scalar_expression> [ [ AS ] property_alias ] } [ ,...n ]  

参数Arguments

  • <select_specification>

    要为结果集选择的属性或值。Properties or value to be selected for the result set.

  • '*'

    指定应当在不进行任何更改的情况下检索值。Specifies that the value should be retrieved without making any changes. 指定如果处理的值是一个对象,则将检索所有属性。Specifically if the processed value is an object, all properties will be retrieved.

  • <object_property_list>

    指定要检索的属性的列表。Specifies the list of properties to be retrieved. 每个返回值都是具有指定属性的对象。Each returned value will be an object with the properties specified.

  • VALUE

    指定应当检索 JSON 值而非整个 JSON 对象。Specifies that the JSON value should be retrieved instead of the complete JSON object. 不同于 <property_list>,这不会将投影的值包装在对象中。This, unlike <property_list> does not wrap the projected value in an object.

  • DISTINCT

    指定应删除投影属性的重复项。Specifies that duplicates of projected properties should be removed.

  • <scalar_expression>

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

备注Remarks

只有 FROM 子句仅声明了一个别名时,SELECT * 语法才有效。The SELECT * syntax is only valid if FROM clause has declared exactly one alias. SELECT * 提供了标识投影,在不需要投影时可能十分有用。SELECT * provides an identity projection, which can be useful if no projection is needed. 只有当指定了 FROM 子句并且仅引入了单个输入源时,SELECT * 才有效。SELECT * is only valid if FROM clause is specified and introduced only a single input source.

SELECT <select_list>SELECT * 是“语法糖”,可另外使用简单的 SELECT 语句表示,如下所示。Both SELECT <select_list> and SELECT * are "syntactic sugar" and can be alternatively expressed by using simple SELECT statements as shown below.

  1. SELECT * FROM ... AS from_alias ...

    等效于:is equivalent to:

    SELECT from_alias FROM ... AS from_alias ...

  2. SELECT <expr1> AS p1, <expr2> AS p2,..., <exprN> AS pN [other clauses...]

    等效于:is equivalent to:

    SELECT VALUE { p1: <expr1>, p2: <expr2>, ..., pN: <exprN> }[other clauses...]

示例Examples

以下 SELECT 查询示例从 id 匹配 AndersenFamilyFamilies 中返回 addressThe following SELECT query example returns address from Families whose id matches AndersenFamily:

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

其结果是:The results are:

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

带引号的属性访问器Quoted property accessor

可以使用带引号的属性运算符 [] 访问属性。You can access properties using the quoted property operator []. 例如,由于再也无法解析标识符“Families”,因此 SELECT c.grade and SELECT c["grade"] 是等效的。For example, SELECT c.grade and SELECT c["grade"] are equivalent. 此语法很适合用于转义包含空格和特殊字符的属性,或者其名称与 SQL 关键字或保留字相同的属性。This syntax is useful to escape a property that contains spaces, special characters, or has the same name as a SQL keyword or reserved word.

    SELECT f["lastName"]
    FROM Families f
    WHERE f["id"] = "AndersenFamily"

嵌套属性Nested properties

以下示例投影两个嵌套属性:f.address.statef.address.cityThe following example projects two nested properties, f.address.state and f.address.city.

    SELECT f.address.state, f.address.city
    FROM Families f
    WHERE f.id = "AndersenFamily"

其结果是:The results are:

    [{
      "state": "WA",
      "city": "Seattle"
    }]

JSON 表达式JSON expressions

投影也支持 JSON 表达式,如以下示例所示:Projection also supports JSON expressions, as shown in the following example:

    SELECT { "state": f.address.state, "city": f.address.city, "name": f.id }
    FROM Families f
    WHERE f.id = "AndersenFamily"

其结果是:The results are:

    [{
      "$1": {
        "state": "WA",
        "city": "Seattle",
        "name": "AndersenFamily"
      }
    }]

在上述示例中,SELECT 子句需要创建一个 JSON 对象;由于该示例未提供键,因此子句使用了隐式参数变量名称 $1In the preceding example, the SELECT clause needs to create a JSON object, and since the sample provides no key, the clause uses the implicit argument variable name $1. 以下查询返回两个隐式参数变量:$1$2The following query returns two implicit argument variables: $1 and $2.

    SELECT { "state": f.address.state, "city": f.address.city },
           { "name": f.id }
    FROM Families f
    WHERE f.id = "AndersenFamily"

其结果是:The results are:

    [{
      "$1": {
        "state": "WA",
        "city": "Seattle"
      }, 
      "$2": {
        "name": "AndersenFamily"
      }
    }]

后续步骤Next steps