用于 Azure Cosmos DB 的 SQL 查询示例SQL query examples for Azure Cosmos DB

Azure Cosmos DB SQL API 帐户支持使用 结构化查询语言 (SQL) 作为 JSON 查询语言来查询项。Azure Cosmos DB SQL API accounts support querying items using Structured Query Language (SQL) as a JSON query language. Azure Cosmos DB 查询语言的设计目标是:The design goals of the Azure Cosmos DB query language are to:

  • 支持用户最熟悉的、最流行的 SQL 查询语言,而不是要发明一种新的查询语言。Support SQL, one of the most familiar and popular query languages, instead of inventing a new query language. SQL 提供正式的编程模型用于对 JSON 项进行丰富查询。SQL provides a formal programming model for rich queries over JSON items.

  • 使用 JavaScript 的编程模型作为查询语言的基础。Use JavaScript's programming model as the foundation for the query language. JavaScript 的类型系统、表达式计算和函数调用是 SQL API 的根。JavaScript's type system, expression evaluation, and function invocation are the roots of the SQL API. 这些根为关系投影、跨 JSON 项的分层导航、自联接、空间查询以及调用完全采用 JavaScript 编写的用户定义的函数 (UDF) 等功能提供自然编程模型。These roots provide a natural programming model for features like relational projections, hierarchical navigation across JSON items, self-joins, spatial queries, and invocation of user-defined functions (UDFs) written entirely in JavaScript.

本文基于简单的 JSON 项来逐步讲解一些示例 SQL 查询。This article walks you through some example SQL queries on simple JSON items. 若要详细了解 Azure Cosmos DB SQL 语言语法,请参阅 SQL 语法参考To learn more about Azure Cosmos DB SQL language syntax, see SQL syntax reference.

SQL 查询入门Get started with SQL queries

在 Cosmos DB SQL API 帐户中,创建名为 Families 的容器。In your SQL API Cosmos DB account, create a container called Families. 在该容器中创建两个简单的 JSON 项,然后针对这些项运行几个简单的查询。Create two simple JSON items in the container, and run a few simple queries against them.

创建 JSON 项Create JSON items

以下代码创建两个有关家庭的简单 JSON 项。The following code creates two simple JSON items about families. Andersen 和 Wakefield 家庭的简单 JSON 项包括父母、孩子及其宠物、地址和注册信息。The simple JSON items for the Andersen and Wakefield families include parents, children and their pets, address, and registration information. 第一个项包含字符串、数字、布尔、数组和嵌套属性。The first item has strings, numbers, Booleans, arrays, and nested properties.

{
  "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
}

第二个项使用 givenNamefamilyName,而不是使用 firstNamelastNameThe second item uses givenName and familyName instead of firstName and lastName.

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female", 
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

查询 JSON 项Query the JSON items

尝试对此 JSON 数据执行一些查询来了解 Azure Cosmos DB 的 SQL 查询语言的一些重要方面。Try a few queries against the JSON data to understand some of the key aspects of Azure Cosmos DB's SQL query language.

以下查询返回其中的 id 字段与 AndersenFamily 匹配的项。The following query returns the items where the id field matches AndersenFamily. 由于它是一个 SELECT * 查询,因此该查询的输出是完整的 JSON 项。Since it's a SELECT * query, the output of the query is the complete JSON item. 有关 SELECT 语法的详细信息,请参阅 SELECT 语句For more information about SELECT syntax, see SELECT statement.

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

查询结果为:The query 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
    }]

以下查询将 JSON 输出的格式重新设置为不同的形式。The following query reformats the JSON output into a different shape. 当地址中的城市名称与州名称相同时,该查询将使用两个选定的字段 NameCity 来投影新的 JSON Family 对象。The query projects a new JSON Family object with two selected fields, Name and City, when the address city is the same as the state. “NY, NY”符合这种情况。"NY, NY" matches this case.

    SELECT {"Name":f.id, "City":f.address.city} AS Family
    FROM Families f
    WHERE f.address.city = f.address.state

查询结果为:The query results are:

    [{
        "Family": {
            "Name": "WakefieldFamily",
            "City": "NY"
        }
    }]

以下查询返回 id 与按居住城市排序的 WakefieldFamily 匹配的家庭中所有子女的给定名称。The following query returns all the given names of children in the family whose id matches WakefieldFamily, ordered by the city of residence.

    SELECT c.givenName
    FROM Families f
    JOIN c IN f.children
    WHERE f.id = 'WakefieldFamily'
    ORDER BY f.address.city ASC

其结果是:The results are:

    [
      { "givenName": "Jesse" },
      { "givenName": "Lisa"}
    ]

上述示例演示了 Cosmos DB 查询语言的几个方面:The preceding examples show several aspects of the Cosmos DB query language:

  • 由于 SQL API 适用于 JSON 值,因此它可以处理三种形式的实体,而不是行和列。Since SQL API works on JSON values, it deals with tree-shaped entities instead of rows and columns. 可以引用任意深度的树节点(例如 Node1.Node2.Node3…..Nodem),类似于 ANSI SQL 中的 <table>.<column> 的两部分引用。You can refer to the tree nodes at any arbitrary depth, like Node1.Node2.Node3…..Nodem, similar to the two-part reference of <table>.<column> in ANSI SQL.

  • 由于查询语言适用于无架构数据,因此,必须动态绑定类型系统。Because the query language works with schemaless data, the type system must be bound dynamically. 相同的表达式在不同项上可能会产生不同的类型。The same expression could yield different types on different items. 查询的结果是有效的 JSON 值,但不保证它是固定的架构。The result of a query is a valid JSON value, but isn't guaranteed to be of a fixed schema.

  • Azure Cosmos DB 仅支持严格的 JSON 项。Azure Cosmos DB supports strict JSON items only. 类型系统和表达式仅限于处理 JSON 类型。The type system and expressions are restricted to deal only with JSON types. 有关详细信息,请参阅 JSON 规范For more information, see the JSON specification.

  • Cosmos DB 容器是 JSON 项的一个无架构集合。A Cosmos DB container is a schema-free collection of JSON items. 容器项内部以及跨容器项的关系是按包含关系隐式捕获的,而不是按主键和外键关系捕获的。The relations within and across container items are implicitly captured by containment, not by primary key and foreign key relations. 此特性对于本文稍后要讨论的项内联接非常重要。This feature is important for the intra-item joins discussed later in this article.

SELECT 子句SELECT clause

每个查询按 ANSI SQL 标准由 SELECT 子句和可选的 FROM 和 WHERE 子句组成。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. 有关语法的详细信息,请参阅 SELECT 语句For more information about the syntax, see SELECT statement.

以下 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"
      }
    }]

VALUE 关键字VALUE keyword

VALUE 关键字提供一种只返回 JSON 值的方式。The VALUE keyword provides a way to return the JSON value alone. 例如,下面所示的查询返回标量表达式 "Hello World" 而不是 {$1: "Hello World"}For example, the query shown below returns the scalar expression "Hello World" instead of {$1: "Hello World"}:

    SELECT VALUE "Hello World"

以下查询返回不带 address 标签的 JSON 值:The following query returns the JSON values without the address label:

    SELECT VALUE f.address
    FROM Families f

其结果是:The results are:

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

以下示例演示如何返回 JSON 基元值(JSON 树的叶级别):The following example shows how to return JSON primitive values (the leaf level of the JSON tree):

    SELECT VALUE f.address.state
    FROM Families f

其结果是:The results are:

    [
      "WA",
      "NY"
    ]

DISTINCT 关键字DISTINCT Keyword

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

SELECT DISTINCT VALUE f.lastName
FROM Families f

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

其结果是: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"
        ]
    }
]

别名Aliasing

可以显式为查询中的值指定别名。You can explicitly alias values in queries. 如果查询包含两个同名的属性,请使用别名来重命名其中一个或两个属性,以便可以在投影的结果中消除其歧义。If a query has two properties with the same name, use aliasing to rename one or both of the properties so they're disambiguated in the projected result.

如以下示例所示,将第二个值投影为 NameInfo 时,用于别名的 AS 关键字是可选的:The AS keyword used for aliasing is optional, as shown in the following example when projecting the second value as NameInfo:

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

其结果是:The results are:

    [{
      "AddressInfo": {
        "state": "WA",
        "city": "Seattle"
      },
      "NameInfo": {
        "name": "AndersenFamily"
      }
    }]

FROM 子句FROM clause

FROM (FROM <from_specification>) 子句是可选的,除非稍后在查询中对源进行筛选或投影。The FROM (FROM <from_specification>) clause is optional, unless the source is filtered or projected later in the query. 有关语法的详细信息,请参阅 FROM 语法For more information about the syntax, see FROM syntax. SELECT * FROM Families 之类的查询枚举整个 Families 容器。A query like SELECT * FROM Families enumerates over the entire Families container. 还可以对容器使用特殊标识符 ROOT,而无需使用容器名称。You can also use the special identifier ROOT for the container instead of using the container name.

FROM 子句对每个查询强制实施以下规则:The FROM clause enforces the following rules per query:

  • 容器可以使用别名,如 SELECT f.id FROM Families AS f 或只需为 SELECT f.id FROM Families fThe container can be aliased, such as SELECT f.id FROM Families AS f or simply SELECT f.id FROM Families f. 此处的 fFamilies 的别名。Here f is the alias for Families. AS 是可选的关键字,用于指定标识符的别名。AS is an optional keyword to alias the identifier.

  • 指定别名后,无法绑定原始的源名称。Once aliased, the original source name cannot be bound. 例如,SELECT Families.id FROM Families f 在语法上是无效的,原因是标识符 Families 已指定别名,因此不再可以解析。For example, SELECT Families.id FROM Families f is syntactically invalid because the identifier Families has been aliased and can't be resolved anymore.

  • 所有被引用的属性必须完全限定,以避免在不严格遵守架构时出现任何有歧义的绑定。All referenced properties must be fully qualified, to avoid any ambiguous bindings in the absence of strict schema adherence. 例如,SELECT id FROM Families f 在语法上是无效的,因为未绑定属性 idFor example, SELECT id FROM Families f is syntactically invalid because the property id isn't bound.

使用 FROM 子句获取子项Get subitems by using the FROM clause

FROM 子句可将源化简为更小的子集。The FROM clause can reduce the source to a smaller subset. 要在每个项中仅枚举子树,子根可能会变成源,如以下示例所示:To enumerate only a subtree in each item, the subroot can become the source, as shown in the following example:

    SELECT *
    FROM Families.children

其结果是:The results are:

    [
      [
        {
            "firstName": "Henriette Thaulow",
            "gender": "female",
            "grade": 5,
            "pets": [
              {
                  "givenName": "Fluffy"
              }
            ]
        }
      ],
      [
       {
            "familyName": "Merriam",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1
        },
        {
            "familyName": "Miller",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
      ]
    ]

上述查询使用数组作为源,但你也可以使用对象作为源。The preceding query used an array as the source, but you can also use an object as the source. 该查询考虑将源中任何有效的已定义 JSON 值包含在结果中。The query considers any valid, defined JSON value in the source for inclusion in the result. 以下示例将排除不带 address.state 值的 FamiliesThe following example would exclude Families that don't have an address.state value.

    SELECT *
    FROM Families.address.state

其结果是:The results are:

    [
      "WA",
      "NY"
    ]

WHERE 子句WHERE clause

可选的 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. 有关语法的详细信息,请参阅 WHERE 语法For more information about the syntax, see WHERE syntax.

以下查询请求包含值为 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.

等式和比较运算符Equality and comparison operators

下表显示了 SQL API 中任意两个 JSON 类型之间等式比较的结果。The following table shows the result of equality comparisons in the SQL API between any two JSON types.

OpOp UndefinedUndefined NullNull 布尔值Boolean 数字Number 字符串String ObjectObject 数组Array
UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined
NullNull UndefinedUndefined 正常Ok UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined
布尔值Boolean UndefinedUndefined UndefinedUndefined 正常Ok UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined
数字Number UndefinedUndefined UndefinedUndefined UndefinedUndefined 正常Ok UndefinedUndefined UndefinedUndefined UndefinedUndefined
字符串String UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined 正常Ok UndefinedUndefined UndefinedUndefined
ObjectObject UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined 正常Ok UndefinedUndefined
数组Array UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined UndefinedUndefined 正常Ok

对于 >>=!=<<= 等比较运算符,跨类型的比较或者两个对象或数组之间的比较会生成 UndefinedFor comparison operators such as >, >=, !=, <, and <=, comparison across types or between two objects or arrays produces Undefined.

如果标量表达式的结果为 Undefined,则不会将该项包含在结果中,因为 Undefined 不等于 trueIf the result of the scalar expression is Undefined, the item isn't included in the result, because Undefined doesn't equal true.

逻辑(AND、OR 和 NOT)运算符Logical (AND, OR and NOT) operators

逻辑运算符对布尔值进行运算。Logical operators operate on Boolean values. 下表显示了这些运算符的逻辑真值表:The following tables show the logical truth tables for these operators:

OR 运算符OR operator

OROR TrueTrue FalseFalse UndefinedUndefined
TrueTrue TrueTrue TrueTrue TrueTrue
FalseFalse TrueTrue FalseFalse UndefinedUndefined
UndefinedUndefined TrueTrue UndefinedUndefined UndefinedUndefined

AND 运算符AND operator

ANDAND TrueTrue FalseFalse UndefinedUndefined
TrueTrue TrueTrue FalseFalse UndefinedUndefined
FalseFalse FalseFalse FalseFalse FalseFalse
UndefinedUndefined UndefinedUndefined FalseFalse UndefinedUndefined

NOT 运算符NOT operator

NOTNOT
TrueTrue FalseFalse
FalseFalse TrueTrue
UndefinedUndefined UndefinedUndefined

BETWEEN 关键字BETWEEN keyword

与在 ANSI SQL 中一样,可以使用 BETWEEN 关键字来对字符串或数字值的范围表达查询。As in ANSI SQL, 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

与在 ANSI-SQL 中不同,你还可以在 FROM 子句中使用 BETWEEN 子句,如以下示例所示。Unlike in ANSI SQL, you can also use the BETWEEN clause in the FROM 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.

IN 关键字IN keyword

使用 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")

* 运算符* operator

特殊运算符 * 按原样投影整个项。The special operator * projects the entire item as is. 在使用时,它必须仅为投影的字段。When used, it must be the only projected field. 类似于 SELECT * FROM Families f 的查询是有效的,而 SELECT VALUE * FROM Families fSELECT *, f.id FROM Families f 是无效的。A query like SELECT * FROM Families f is valid, but SELECT VALUE * FROM Families f and SELECT *, f.id FROM Families f are not valid. 本文中的第一个查询使用了 * 运算符。The first query in this article used the * operator.

?? 和 ??and ?? 运算符operators

如同在 C# 和 JavaScript 等编程语言中那样,可以使用三元 (?) 和联合 (??) 运算符来生成条件表达式。You can use the Ternary (?) and Coalesce (??) operators to build conditional expressions, as in programming languages like C# and JavaScript.

可以使用 ?You can use the ? 运算符即时构造新的 JSON 属性。operator to construct new JSON properties on the fly. 例如,以下查询将年级分类为 elementaryotherFor example, the following query classifies grade levels into elementary or other:

     SELECT (c.grade < 5)? "elementary": "other" AS gradeLevel
     FROM Families.children[0] c

还可以将调用嵌套到 ?You can also nest calls to the ? 运算符,如以下查询中所示:operator, as in the following query:

    SELECT (c.grade < 5)? "elementary": ((c.grade < 9)? "junior": "high") AS gradeLevel
    FROM Families.children[0] c

与其他查询运算符一样,As with other query operators, the ? 如果引用的属性缺失或者要比较的类型不同,则 ? 运算符将会排除项。operator excludes items if the referenced properties are missing or the types being compared are different.

查询半结构化Use the ?? 或混合类型的数据时,可以使用 ?? 运算符有效地检查项中的属性。operator to efficiently check for a property in an item when querying against semi-structured or mixed-type data. 例如,以下查询返回 lastName(如果存在)或 surname(如果 lastName 不存在)。For example, the following query returns lastName if present, or surname if lastName isn't present.

    SELECT f.lastName ?? f.surname AS familyName
    FROM Families f

TOP 运算符TOP operator

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. 有关详细信息,请参阅参数化查询部分。For more information, see the Parameterized queries section.

    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
    }]

ORDER BY 子句ORDER BY clause

与在 ANSI SQL 中一样,可以在查询中包含可选的 ORDER BY 子句。As in ANSI SQL, you can include an optional ORDER BY clause in queries. 可选的 ASC 或 DESC 参数指定是要按升序还是降序检索结果。The optional ASC or DESC argument specifies whether to retrieve results in ascending or descending order. 默认值为 ASC。ASC is the default.

例如,以下查询按居住城市名称的升序检索家庭: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.

OFFSET LIMIT 子句OFFSET LIMIT clause

OFFSET LIMIT 是一个可选子句,它会跳过然后提取查询中特定数目的值。OFFSET LIMIT is an optional clause to skip then take some number of values from the query. 必须在 OFFSET LIMIT 子句中指定 OFFSET 计数和 LIMIT 计数。The OFFSET count and the LIMIT count are required in the OFFSET LIMIT clause.

将 OFFSET LIMIT 与 ORDER BY 子句结合使用时,将通过跳过然后提取排序值来生成结果集。When OFFSET LIMIT is used in conjunction with an ORDER BY clause, the result set is produced by doing skip and take on the ordered values. 如果不使用 ORDER BY 子句,则会生成值的确定顺序。If no ORDER BY clause is used, it will result in a deterministic order of values.

例如,以下查询跳过第一个值并返回第二个值(按居住城市名称的顺序):For example, here's a query that skips the first value and returns the second value (in order of the resident city's name):

    SELECT f.id, f.address.city
    FROM Families f
    ORDER BY f.address.city
    OFFSET 1 LIMIT 1

其结果是:The results are:

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

以下查询跳过第一个值并返回第二个值(不排序):Here's a query that skips the first value and returns the second value (without ordering):

   SELECT f.id, f.address.city
    FROM Families f
    OFFSET 1 LIMIT 1

其结果是:The results are:

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

标量表达式Scalar expressions

SELECT 子句支持标量表达式,例如常量、算术表达式和逻辑表达式。The SELECT clause supports scalar expressions like constants, arithmetic expressions, and logical expressions. 以下查询使用一个标量表达式:The following query uses a scalar expression:

    SELECT ((2 + 11 % 7)-2)/3

其结果是:The results are:

    [{
      "$1": 1.33333
    }]

在以下查询中,标量表达式的结果是一个布尔值:In the following query, the result of the scalar expression is a Boolean:

    SELECT f.address.city = f.address.state AS AreFromSameCityState
    FROM Families f

其结果是:The results are:

    [
      {
        "AreFromSameCityState": false
      },
      {
        "AreFromSameCityState": true
      }
    ]

对象和数组创建Object and array creation

SQL API 的一个重要功能是创建数组和对象。A key feature of the SQL API is array and object creation. 以上示例创建了新的 JSON 对象 AreFromSameCityStateThe previous example created a new JSON object, AreFromSameCityState. 还可以构造数组,如以下示例所示:You can also construct arrays, as shown in the following example:

    SELECT [f.address.city, f.address.state] AS CityState
    FROM Families f

其结果是:The results are:

    [
      {
        "CityState": [
          "Seattle",
          "WA"
        ]
      },
      {
        "CityState": [
          "NY", 
          "NY"
        ]
      }
    ]

迭代Iteration

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 the following example:

    SELECT *
    FROM Families.children

其结果是:The results are:

    [
      [
        {
          "firstName": "Henriette Thaulow",
          "gender": "female",
          "grade": 5,
          "pets": [{ "givenName": "Fluffy"}]
        }
      ], 
      [
        {
            "familyName": "Merriam",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1
        }, 
        {
            "familyName": "Miller",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
      ]
    ]

下一个查询循环访问 Families 容器中的 childrenThe next query performs iteration over children in the Families container. 输出的数组与前面的查询不同。The output array is different from the preceding query. 此示例拆分 children 并将结果平展为单个数组:This example splits children, and flattens the results into a single array:

    SELECT *
    FROM c IN Families.children

其结果是:The results are:

    [
      {
          "firstName": "Henriette Thaulow",
          "gender": "female",
          "grade": 5,
          "pets": [{ "givenName": "Fluffy" }]
      },
      {
          "familyName": "Merriam",
          "givenName": "Jesse",
          "gender": "female",
          "grade": 1
      },
      {
          "familyName": "Miller",
          "givenName": "Lisa",
          "gender": "female",
          "grade": 8
      }
    ]

可以进一步筛选该数组的每个条目,如以下示例所示:You can filter further on each individual entry of the array, as shown in the following example:

    SELECT c.givenName
    FROM c IN Families.children
    WHERE c.grade = 8

其结果是:The results are:

    [{
      "givenName": "Lisa"
    }]

还可基于数组迭代的结果进行聚合。You can also aggregate over the result of an array iteration. 例如,以下查询计数所有家庭中的孩子数目。For example, the following query counts the number of children among all families:

    SELECT COUNT(child)
    FROM child IN Families.children

其结果是:The results are:

    [
      {
        "$1": 3
      }
    ]

联接Joins

在关系数据库中,跨表联接是设计规范化架构的逻辑定理。In a relational database, joins across tables are the logical corollary to designing normalized schemas. 相比之下,SQL API 使用无架构项的反规范化数据模型,这在逻辑上等效于自联接。In contrast, the SQL API uses the denormalized data model of schema-free items, which is the logical equivalent of a self-join.

该语言支持语法 <from_source1> JOIN <from_source2> JOIN ... JOIN <from_sourceN>The language supports the syntax <from_source1> JOIN <from_source2> JOIN ... JOIN <from_sourceN>. 此查询返回一组包含 N 值的元组。This query returns a set of tuples with N values. 每个元组拥有通过对它们相应的集遍历所有容器别名所产生的值。Each tuple has values produced by iterating all container aliases over their respective sets. 换言之,此查询是参与联接的集的完整叉积。In other words, this query does a full cross product of the sets participating in the join.

下面的示例演示了 JOIN 子句的工作原理。The following examples show how the JOIN clause works. 在以下示例中,由于源中每个项和空集的叉积为空,因此结果为空:In the following example, the result is empty, since the cross product of each item from source and an empty set is empty:

    SELECT f.id
    FROM Families f
    JOIN f.NonExistent

结果为:The result is:

    [{
    }]

在以下示例中,联接是两个 JSON 对象、项根 id 和子根 children 之间的叉积。In the following example, the join is a cross product between two JSON objects, the item root id and the children subroot. children 是数组这一事实在联接中不起作用,因为查询处理的是作为 children 数组的单一根。The fact that children is an array isn't effective in the join, because it deals with a single root that is the children array. 由于每个带有数组的项的叉积仅生成一个项,因此结果仅包含两个结果。The result contains only two results, because the cross product of each item with the array yields exactly only one item.

    SELECT f.id
    FROM Families f
    JOIN f.children

其结果是:The results are:

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

下面的示例演示了更传统的联接:The following example shows a more conventional join:

    SELECT f.id
    FROM Families f
    JOIN c IN f.children

其结果是:The results are:

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

JOIN 子句的 FROM 源是一个迭代器。The FROM source of the JOIN clause is an iterator. 因此,以上示例中的流程为:So, the flow in the preceding example is:

  1. 展开数组中的每个子元素 cExpand each child element c in the array.
  2. 应用包含项 f 的根的叉积,该项包含已在第一个步骤中平展的每个子元素 cApply a cross product with the root of the item f with each child element c that the first step flattened.
  3. 最后,单独投影根对象 f id 属性。Finally, project the root object f id property alone.

第一个项 (AndersenFamily) 仅包含一个 children 元素,因此结果集仅包含单个对象。The first item, AndersenFamily, contains only one children element, so the result set contains only a single object. 第二个项 WakefieldFamily 包含两个 children,因此,叉积为每个 children 元素生成一个对象,共两个对象。The second item, WakefieldFamily, contains two children, so the cross product produces two objects, one for each children element. 这两个项中的根字段会是相同的,正如在叉积中所预期的一样。The root fields in both these items are the same, just as you would expect in a cross product.

JOIN 子句真正实用的地方是通过以其他方式难以投影的形式基于叉积生成元组。The real utility of the JOIN clause is to form tuples from the cross product in a shape that's otherwise difficult to project. 以下示例对元组组合进行筛选,让用户选择元组在整体上满足的条件。The example below filters on the combination of a tuple that lets the user choose a condition satisfied by the tuples overall.

    SELECT 
        f.id AS familyName,
        c.givenName AS childGivenName,
        c.firstName AS childFirstName,
        p.givenName AS petName
    FROM Families f
    JOIN c IN f.children
    JOIN p IN c.pets

其结果是:The results are:

    [
      {
        "familyName": "AndersenFamily",
        "childFirstName": "Henriette Thaulow",
        "petName": "Fluffy"
      },
      {
        "familyName": "WakefieldFamily",
        "childGivenName": "Jesse",
        "petName": "Goofy"
      }, 
      {
       "familyName": "WakefieldFamily",
       "childGivenName": "Jesse",
       "petName": "Shadow"
      }
    ]

以下示例对前一个示例做了延伸,将会执行双重联接。The following extension of the preceding example performs a double join. 可将叉积视为下面所示的伪代码:You could view the cross product as the following pseudo-code:

    for-each(Family f in Families)
    {
        for-each(Child c in f.children)
        {
            for-each(Pet p in c.pets)
            {
                return (Tuple(f.id AS familyName,
                  c.givenName AS childGivenName,
                  c.firstName AS childFirstName,
                  p.givenName AS petName));
            }
        }
    }

AndersenFamily 中有一个孩子拥有一只宠物,因此叉积从此家庭生成了一行 (1*1*1)。AndersenFamily has one child who has one pet, so the cross product yields one row (1*1*1) from this family. WakefieldFamily 中有两个孩子,其中只有一个孩子拥有宠物,但这个孩子拥有两只宠物。WakefieldFamily has two children, only one of whom has pets, but that child has two pets. 叉积对此家庭生成了 1*1*2 = 2 行。The cross product for this family yields 1*1*2 = 2 rows.

以下示例根据 pet 进行了额外的筛选,这排除了宠物名称不是 Shadow 的所有元组。In the next example, there is an additional filter on pet, which excludes all the tuples where the pet name is not Shadow. 可以基于数组生成元组,根据元组的任意元素进行筛选以及投影元素的任何组合。You can build tuples from arrays, filter on any of the elements of the tuple, and project any combination of the elements.

    SELECT 
        f.id AS familyName,
        c.givenName AS childGivenName,
        c.firstName AS childFirstName,
        p.givenName AS petName
    FROM Families f
    JOIN c IN f.children
    JOIN p IN c.pets
    WHERE p.givenName = "Shadow"

其结果是:The results are:

    [
      {
       "familyName": "WakefieldFamily",
       "childGivenName": "Jesse",
       "petName": "Shadow"
      }
    ]

用户定义的函数 (UDF)User-defined functions (UDFs)

SQL API 支持用户定义函数 (UDF)。The SQL API provides support for user-defined functions (UDFs). 使用标量 UDF,可以传入零个或多个参数,并返回单个参数结果。With scalar UDFs, you can pass in zero or many arguments and return a single argument result. API 会检查每个参数 JSON 值是否合法。The API checks each argument for being legal JSON values.

API 扩展了 SQL 语法,支持使用 UDF 的自定义应用程序逻辑。The API extends the SQL syntax to support custom application logic using UDFs. 可将 UDF 注册到 SQL API,然后在 SQL 查询中引用它们。You can register UDFs with the SQL API, and reference them in SQL queries. 事实上,UDF 经过精心设计,可从查询调用。In fact, the UDFs are exquisitely designed to call from queries. 作为一种定理,UDF 不能像其他 JavaScript 类型(例如存储过程和触发器)一样访问上下文对象。As a corollary, UDFs do not have access to the context object like other JavaScript types, such as stored procedures and triggers. 查询是只读的,可以在主要或次要副本上运行。Queries are read-only, and can run either on primary or secondary replicas. 与其他 JavaScript 类型不同,UDF 只能在次要副本上运行。UDFs, unlike other JavaScript types, are designed to run on secondary replicas.

以下示例在 Cosmos DB 数据库中的某个项容器下注册一个 UDF。The following example registers a UDF under an item container in the Cosmos DB database. 该示例创建了名为 REGEX_MATCH 的 UDF。The example creates a UDF whose name is REGEX_MATCH. 它接受两个 JSON 字符串值:inputpattern,并使用 JavaScript 的 string.match() 函数检查第一个值是否与第二个值中指定的模式相匹配。It accepts two JSON string values, input and pattern, and checks if the first matches the pattern specified in the second using JavaScript's string.match() function.

       UserDefinedFunction regexMatchUdf = new UserDefinedFunction
       {
           Id = "REGEX_MATCH",
           Body = @"function (input, pattern) {
                      return input.match(pattern) !== null;
                   };",
       };

       UserDefinedFunction createdUdf = client.CreateUserDefinedFunctionAsync(
           UriFactory.CreateDocumentCollectionUri("myDatabase", "families"),
           regexMatchUdf).Result;  

现在,请在查询投影中使用此 UDF。Now, use this UDF in a query projection. 从查询内部调用 UDF 时,必须使用区分大小写的前缀 udf. 来限定 UDF。You must qualify UDFs with the case-sensitive prefix udf. when calling them from within queries.

    SELECT udf.REGEX_MATCH(Families.address.city, ".*eattle")
    FROM Families

其结果是:The results are:

    [
      {
        "$1": true
      },
      {
        "$1": false
      }
    ]

可以在筛选器中使用以 udf. 前缀限定的 UDF,如以下示例所示:You can use the UDF qualified with the udf. prefix inside a filter, as in the following example:

    SELECT Families.id, Families.address.city
    FROM Families
    WHERE udf.REGEX_MATCH(Families.address.city, ".*eattle")

其结果是:The results are:

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

从本质上来说,UDF 是可以在投影和筛选器中使用的有效标量表达式。In essence, UDFs are valid scalar expressions that you can use in both projections and filters.

为了进一步了解 UDF 的强大功能,让我们查看使用条件逻辑的另一个示例:To expand on the power of UDFs, look at another example with conditional logic:

       UserDefinedFunction seaLevelUdf = new UserDefinedFunction()
       {
           Id = "SEALEVEL",
           Body = @"function(city) {
                   switch (city) {
                       case 'Seattle':
                           return 520;
                       case 'NY':
                           return 410;
                       case 'Shanghai':
                           return 673;
                       default:
                           return -1;
                    }"
            };

            UserDefinedFunction createdUdf = await client.CreateUserDefinedFunctionAsync(
                UriFactory.CreateDocumentCollectionUri("myDatabase", "families"),
                seaLevelUdf);

以下示例运用了 UDF:The following example exercises the UDF:

    SELECT f.address.city, udf.SEALEVEL(f.address.city) AS seaLevel
    FROM Families f

其结果是:The results are:

     [
      {
        "city": "Seattle",
        "seaLevel": 520
      },
      {
        "city": "NY",
        "seaLevel": 410
      }
    ]

如果 UDF 参数引用的属性在 JSON 值中未提供,则会将该参数视为未定义,因此会跳过 UDF 调用。If the properties referred to by the UDF parameters aren't available in the JSON value, the parameter is considered as undefined and the UDF invocation is skipped. 同样,如果未定义 UDF 的结果,则不会将此 UDF 包含在结果中。Similarly, if the result of the UDF is undefined, it's not included in the result.

如以上示例所示,UDF 将 JavaScript 语言的强大功能与 SQL API 相集成。As the preceding examples show, UDFs integrate the power of JavaScript language with the SQL API. UDF 提供丰富的可编程接口来执行复杂的过程,并借助内置的 JavaScript 运行时功能来执行条件逻辑。UDFs provide a rich programmable interface to do complex procedural, conditional logic with the help of built-in JavaScript runtime capabilities. SQL API 在当前的 WHERE 或 SELECT 子句处理阶段,为每个源项的 UDF 提供参数。The SQL API provides the arguments to the UDFs for each source item at the current WHERE or SELECT clause stage of processing. 结果将无缝整合到总体执行管道中。The result is seamlessly incorporated in the overall execution pipeline. 总而言之,UDF 是在查询过程中执行复杂业务逻辑的极佳工具。In summary, UDFs are great tools to do complex business logic as part of queries.

聚合函数Aggregate functions

聚合函数对 SELECT 子句中的一组值执行计算,并返回单个值。Aggregate functions perform a calculation on a set of values in the SELECT clause and return a single value. 例如,以下查询返回 Families 容器中的项计数。For example, the following query returns the count of items within the Families container:

    SELECT COUNT(1)
    FROM Families f

其结果是:The results are:

    [{
        "$1": 2
    }]

也可以使用 VALUE 关键字来仅返回聚合的标量值。You can also return only the scalar value of the aggregate by using the VALUE keyword. 例如,以下查询将值的计数作为单个值返回:For example, the following query returns the count of values as a single number:

    SELECT VALUE COUNT(1)
    FROM Families f

其结果是:The results are:

    [ 2 ]

还可以将聚合与筛选器结合使用。You can also combine aggregations with filters. 例如,以下查询返回包含 WA 州地址的项计数。For example, the following query returns the count of items with the address state of WA.

    SELECT VALUE COUNT(1)
    FROM Families f
    WHERE f.address.state = "WA"

其结果是:The results are:

    [ 1 ]

SQL API 支持以下聚合函数。The SQL API supports the following aggregate functions. SUM 和 AVG 针对数字值执行运算,COUNT、MIN 和 MAX 可以处理数字、字符串、布尔值和 null。SUM and AVG operate on numeric values, and COUNT, MIN, and MAX work on numbers, strings, Booleans, and nulls.

函数Function 说明Description
COUNTCOUNT 在表达式中返回项的数目。Returns the number of items in the expression.
SUMSUM 在表达式中返回所有值的总和。Returns the sum of all the values in the expression.
最小值MIN 在表达式中返回最小值。Returns the minimum value in the expression.
MAXMAX 在表达式中返回最大值。Returns the maximum value in the expression.
平均值AVG 在表达式中返回多个值的平均值。Returns the average of the values in the expression.

还可以基于数组迭代的结果进行聚合。You can also aggregate over the results of an array iteration. 有关详细信息,请参阅迭代部分。For more information, see the Iteration section.

Note

在 Azure 门户的数据资源管理器中,聚合查询可以仅基于一个查询页面聚合部分结果。In the Azure portal's Data Explorer, aggregation queries may aggregate partial results over only one query page. SDK 跨所有页面生成单个累计值。The SDK produces a single cumulative value across all pages. 若要使用代码执行聚合查询,需要 .NET SDK 1.12.0、.NET Core SDK 1.1.0,或者 Java SDK 1.9.5 或更高版本。To perform aggregation queries using code, you need .NET SDK 1.12.0, .NET Core SDK 1.1.0, or Java SDK 1.9.5 or above.

内置函数Built-in functions

Cosmos DB 还支持使用许多内置函数进行常见操作,这些函数可以在查询(如用户定义的函数 (UDF))中使用。Cosmos DB also supports a number of built-in functions for common operations, which you can use inside queries like user-defined functions (UDFs).

函数组Function group 操作Operations
数学函数Mathematical functions ABS、CEILING、EXP、FLOOR、LOG、LOG10、POWER、ROUND、SIGN、SQRT、SQUARE、TRUNC、ACOS、ASIN、ATAN、ATN2、COS、COT、DEGREES、PI、RADIANS、SIN 和 TANABS, CEILING, EXP, FLOOR, LOG, LOG10, POWER, ROUND, SIGN, SQRT, SQUARE, TRUNC, ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, PI, RADIANS, SIN, TAN
类型检查函数Type-checking functions IS_ARRAY、IS_BOOL、IS_NULL、IS_NUMBER、IS_OBJECT、IS_STRING、IS_DEFINED 和 IS_PRIMITIVEIS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, IS_PRIMITIVE
字符串函数String functions CONCAT、CONTAINS、ENDSWITH、INDEX_OF、LEFT、LENGTH、LOWER、LTRIM、REPLACE、REPLICATE、REVERSE、RIGHT、RTRIM、STARTSWITH、SUBSTRING 和 UPPERCONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, UPPER
数组函数Array functions ARRAY_CONCAT、ARRAY_CONTAINS、ARRAY_LENGTH 和 ARRAY_SLICEARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE
空间函数Spatial functions ST_DISTANCE、ST_WITHIN、ST_INTERSECTS、ST_ISVALID 和 ST_ISVALIDDETAILEDST_DISTANCE, ST_WITHIN, ST_INTERSECTS, ST_ISVALID, ST_ISVALIDDETAILED

如果当前正在使用的用户定义的函数 (UDF) 有内置函数可用,则相应的内置函数会更快更有效地运行。If you're currently using a user-defined function (UDF) for which a built-in function is now available, the corresponding built-in function will be quicker to run and more efficient.

Cosmos DB 函数与 ANSI SQL 函数之间的主要差别在于,Cosmos DB 函数能够很好地处理无架构数据和混合架构数据。The main difference between Cosmos DB functions and ANSI SQL functions is that Cosmos DB functions are designed to work well with schemaless and mixed-schema data. 例如,如果某个属性缺失或包含类似于 unknown 的非数字值,则会跳过该项,而不是返回错误。For example, if a property is missing or has a non-numeric value like unknown, the item is skipped instead of returning an error.

数学函数Mathematical functions

每个数学函数均执行一个计算,基于作为参数提供的输出值,并返回数值。The mathematical functions each perform a calculation, based on input values that are provided as arguments, and return a numeric value. 以下是支持的内置数学函数表。Here's a table of supported built-in mathematical functions.

使用情况Usage 说明Description
ABS (num_expr)ABS (num_expr) 返回指定数值表达式的绝对(正)值。Returns the absolute (positive) value of the specified numeric expression.
CEILING (num_expr)CEILING (num_expr) 返回大于或等于指定数值表达式的最小整数值。Returns the smallest integer value greater than, or equal to, the specified numeric expression.
FLOOR (num_expr)FLOOR (num_expr) 返回小于或等于指定数值表达式的最大整数。Returns the largest integer less than or equal to the specified numeric expression.
EXP (num_expr)EXP (num_expr) 返回指定数值表达式的指数。Returns the exponent of the specified numeric expression.
LOG (num_expr, base)LOG (num_expr, base) 返回指定数值表达式的自然对数,或使用指定底数的对数。Returns the natural logarithm of the specified numeric expression, or the logarithm using the specified base.
LOG10 (num_expr)LOG10 (num_expr) 返回指定数值表达式以 10 为底的对数值。Returns the base-10 logarithmic value of the specified numeric expression.
ROUND (num_expr)ROUND (num_expr) 返回一个数值,四舍五入到最接近的整数值。Returns a numeric value, rounded to the closest integer value.
TRUNC (num_expr)TRUNC (num_expr) 返回一个数值,截断到最接近的整数值。Returns a numeric value, truncated to the closest integer value.
SQRT (num_expr)SQRT (num_expr) 返回指定数值表达式的平方根。Returns the square root of the specified numeric expression.
SQUARE (num_expr)SQUARE (num_expr) 返回指定数值表达式的平方。Returns the square of the specified numeric expression.
POWER (num_expr, num_expr)POWER (num_expr, num_expr) 返回指定数值表达式相对指定值的幂。Returns the power of the specified numeric expression to the value specified.
SIGN (num_expr)SIGN (num_expr) 返回指定数值表达式的符号值 (-1, 0, 1)。Returns the sign value (-1, 0, 1) of the specified numeric expression.
ACOS (num_expr)ACOS (num_expr) 返回角度(弧度),其余弦是指定的数值表达式;也被称为反余弦。Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine.
ASIN (num_expr)ASIN (num_expr) 返回角度(弧度),其正弦是指定的数值表达式。Returns the angle, in radians, whose sine is the specified numeric expression. 此函数也称为反正弦。This function is also called arcsine.
ATAN (num_expr)ATAN (num_expr) 返回角度(弧度),其正切是指定的数值表达式。Returns the angle, in radians, whose tangent is the specified numeric expression. 此函数也称为反正切。This function is also called arctangent.
ATN2 (num_expr)ATN2 (num_expr) 返回正 x 轴与射线(原点到点 (y, x))之间的角度(弧度),其中 x 和 y 是两个指定的浮点表达式的值。Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions.
COS (num_expr)COS (num_expr) 返回指定表达式中指定角度的三角余弦(弧度)。Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
COT (num_expr)COT (num_expr) 返回指定数值表达式中指定角度的三角余切。Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression.
DEGREES (num_expr)DEGREES (num_expr) 返回指定角度(弧度)的相应角度(度)。Returns the corresponding angle in degrees for an angle specified in radians.
PI ()PI () 返回 PI 的常数值。Returns the constant value of PI.
RADIANS (num_expr)RADIANS (num_expr) 返回输入的数值表达式(度)的弧度。Returns radians when a numeric expression, in degrees, is entered.
SIN (num_expr)SIN (num_expr) 返回指定表达式中指定角度的三角正弦(弧度)。Returns the trigonometric sine of the specified angle, in radians, in the specified expression.
TAN (num_expr)TAN (num_expr) 返回指定表达式中输入表达式的正切。Returns the tangent of the input expression, in the specified expression.

可以运行以下示例所示的查询:You can run queries like the following example:

    SELECT VALUE ABS(-4)

结果为:The result is:

    [4]

类型检查函数Type-checking functions

使用类型检查函数可以检查 SQL 查询中表达式的类型。The type-checking functions let you check the type of an expression within a SQL query. 当项中的属性可变或未知时,可以使用类型检查函数即时确定这些属性的类型。You can use type-checking functions to determine the types of properties within items on the fly, when they're variable or unknown. 下表列出了支持的内置类型检查函数:Here's a table of supported built-in type-checking functions:

使用情况Usage 说明Description
IS_ARRAY (expr)IS_ARRAY (expr) 返回一个布尔值,它指示值的类型是否为数组。Returns a Boolean indicating if the type of the value is an array.
IS_BOOL (expr)IS_BOOL (expr) 返回一个布尔值,它指示值的类型是否为布尔。Returns a Boolean indicating if the type of the value is a Boolean.
IS_NULL (expr)IS_NULL (expr) 返回一个布尔值,它指示值的类型是否为 null。Returns a Boolean indicating if the type of the value is null.
IS_NUMBER (expr)IS_NUMBER (expr) 返回一个布尔值,它指示值的类型是否为数字。Returns a Boolean indicating if the type of the value is a number.
IS_OBJECT (expr)IS_OBJECT (expr) 返回一个布尔值,它指示值的类型是否为 JSON 对象。Returns a Boolean indicating if the type of the value is a JSON object.
IS_STRING (expr)IS_STRING (expr) 返回一个布尔值,它指示值的类型是否为字符串。Returns a Boolean indicating if the type of the value is a string.
IS_DEFINED (expr)IS_DEFINED (expr) 返回一个布尔,它指示属性是否已经分配了值。Returns a Boolean indicating if the property has been assigned a value.
IS_PRIMITIVE (expr)IS_PRIMITIVE (expr) 返回一个布尔值,它指示值的类型是字符串、数字、布尔值还是 null。Returns a Boolean indicating if the type of the value is a string, number, Boolean, or null.

使用这些函数可以运行以下示例所示的查询:Using these functions, you can run queries like the following example:

    SELECT VALUE IS_NUMBER(-4)

结果为:The result is:

    [true]

字符串函数String functions

以下标量函数对字符串输入值执行操作,并返回字符串、数字或布尔值。The following scalar functions perform an operation on a string input value and return a string, numeric, or Boolean value. 以下是内置字符串函数表:Here's a table of built-in string functions:

使用情况Usage 说明Description
LENGTH (str_expr)LENGTH (str_expr) 返回指定字符串表达式的字符数。Returns the number of characters of the specified string expression.
CONCAT (str_expr, str_expr [, str_expr])CONCAT (str_expr, str_expr [, str_expr]) 返回一个字符串,该字符串是连接两个或多个字符串值的结果。Returns a string that is the result of concatenating two or more string values.
SUBSTRING (str_expr, num_expr, num_expr)SUBSTRING (str_expr, num_expr, num_expr) 返回部分字符串表达式。Returns part of a string expression.
STARTSWITH (str_expr, str_expr)STARTSWITH (str_expr, str_expr) 返回一个布尔值,指示第一个字符串表达式是否以第二个字符串表达式开头。Returns a Boolean indicating whether the first string expression starts with the second.
ENDSWITH (str_expr, str_expr)ENDSWITH (str_expr, str_expr) 返回一个布尔值,指示第一个字符串表达式是否以第二个字符串表达式结尾。Returns a Boolean indicating whether the first string expression ends with the second.
CONTAINS (str_expr, str_expr)CONTAINS (str_expr, str_expr) 返回一个布尔值,该值指示第一个字符串表达式是否包含第二个字符串表达式。Returns a Boolean indicating whether the first string expression contains the second.
INDEX_OF (str_expr, str_expr)INDEX_OF (str_expr, str_expr) 返回第一个指定的字符串表达式中第一次出现第二个字符串表达式的起始位置,如果未找到字符串,则返回 -1。Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string isn't found.
LEFT (str_expr, num_expr)LEFT (str_expr, num_expr) 返回具有指定字符数的字符串的左侧部分。Returns the left part of a string with the specified number of characters.
RIGHT (str_expr, num_expr)RIGHT (str_expr, num_expr) 返回具有指定字符数的字符串的右侧部分。Returns the right part of a string with the specified number of characters.
LTRIM (str_expr)LTRIM (str_expr) 返回删除前导空格后的字符串表达式。Returns a string expression after it removes leading blanks.
RTRIM (str_expr)RTRIM (str_expr) 返回截断所有尾随空格后的字符串表达式。Returns a string expression after truncating all trailing blanks.
LOWER (str_expr)LOWER (str_expr) 返回在将大写字符数据转换为小写后的字符串表达式。Returns a string expression after converting uppercase character data to lowercase.
UPPER (str_expr)UPPER (str_expr) 返回在将小写字符数据转换为大写后的字符串表达式。Returns a string expression after converting lowercase character data to uppercase.
REPLACE (str_expr, str_expr, str_expr)REPLACE (str_expr, str_expr, str_expr) 将出现的所有指定字符串值替换为另一个字符串值。Replaces all occurrences of a specified string value with another string value.
REPLICATE (str_expr, num_expr)REPLICATE (str_expr, num_expr) 将一个字符串值重复指定的次数。Repeats a string value a specified number of times.
REVERSE (str_expr)REVERSE (str_expr) 返回字符串值的逆序排序形式。Returns the reverse order of a string value.

使用这些函数可以运行如下以下所示的查询,该查询返回大写形式的家庭 idUsing these functions, you can run queries like the following, which returns the family id in uppercase:

    SELECT VALUE UPPER(Families.id)
    FROM Families

其结果是:The results are:

    [
        "WAKEFIELDFAMILY",
        "ANDERSENFAMILY"
    ]

或者,如以下示例所示连接字符串:Or concatenate strings, like in this example:

    SELECT Families.id, CONCAT(Families.address.city, ",", Families.address.state) AS location
    FROM Families

其结果是:The results are:

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

还可以在 WHERE 子句中使用字符串函数来筛选结果,如以下示例所示:You can also use string functions in the WHERE clause to filter results, like in the following example:

    SELECT Families.id, Families.address.city
    FROM Families
    WHERE STARTSWITH(Families.id, "Wakefield")

其结果是:The results are:

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

数组函数Array functions

以下标量函数对数组输入值执行操作,并返回数字值、布尔值或数组值。The following scalar functions perform an operation on an array input value and return a numeric, Boolean, or array value. 以下是内置数组函数表:Here's a table of built-in array functions:

使用情况Usage 说明Description
ARRAY_LENGTH (arr_expr)ARRAY_LENGTH (arr_expr) 返回指定数组表达式的元素数。Returns the number of elements of the specified array expression.
ARRAY_CONCAT (arr_expr, arr_expr [, arr_expr])ARRAY_CONCAT (arr_expr, arr_expr [, arr_expr]) 返回一个数组,该数组是连接两个或更多数组值的结果。Returns an array that is the result of concatenating two or more array values.
ARRAY_CONTAINS (arr_expr, expr [, bool_expr])ARRAY_CONTAINS (arr_expr, expr [, bool_expr]) 返回一个布尔,它指示数组是否包含指定的值。Returns a Boolean indicating whether the array contains the specified value. 可以指定是要执行完全还是部分匹配。Can specify if the match is full or partial.
ARRAY_SLICE (arr_expr, num_expr [, num_expr])ARRAY_SLICE (arr_expr, num_expr [, num_expr]) 返回部分数组表达式。Returns part of an array expression.

使用数组函数可以处理 JSON 中的数组。Use array functions to manipulate arrays within JSON. 例如,以下查询返回其中一个 parentsRobin Wakefield 的所有项 idFor example, here's a query that returns all item ids where one of the parents is Robin Wakefield:

    SELECT Families.id 
    FROM Families 
    WHERE ARRAY_CONTAINS(Families.parents, { givenName: "Robin", familyName: "Wakefield" })

结果为:The result is:

    [{
      "id": "WakefieldFamily"
    }]

可以指定一个部分片段来匹配数组中的元素。You can specify a partial fragment for matching elements within the array. 以下查询查找包含 givenNameRobinparents 的所有项 idThe following query finds all item ids that have parents with the givenName of Robin:

    SELECT Families.id 
    FROM Families 
    WHERE ARRAY_CONTAINS(Families.parents, { givenName: "Robin" }, true)

结果为:The result is:

    [{
      "id": "WakefieldFamily"
    }]

下面是使用 ARRAY_LENGTH 获取每个家庭的 children 数目的另一个示例:Here's another example that uses ARRAY_LENGTH to get the number of children per family:

    SELECT Families.id, ARRAY_LENGTH(Families.children) AS numberOfChildren
    FROM Families 

其结果是:The results are:

    [{
      "id": "WakefieldFamily",
      "numberOfChildren": 2
    },
    {
      "id": "AndersenFamily",
      "numberOfChildren": 1
    }]

空间函数Spatial functions

Cosmos DB 支持以下用于查询地理空间的开放地理空间信息联盟 (OGC) 内置函数:Cosmos DB supports the following Open Geospatial Consortium (OGC) built-in functions for geospatial querying:

使用情况Usage 说明Description
ST_DISTANCE (point_expr、point_expr)ST_DISTANCE (point_expr, point_expr) 返回两个 GeoJSON PointPolygonLineString 表达式之间的距离。Returns the distance between the two GeoJSON Point, Polygon, or LineString expressions.
T_WITHIN (point_expr, polygon_expr)T_WITHIN (point_expr, polygon_expr) 返回一个布尔表达式,指示第一个 GeoJSON 对象(PointPolygonLineString)是否在第二个 GeoJSON 对象(PointPolygonLineString)的内部。Returns a Boolean expression indicating whether the first GeoJSON object (Point, Polygon, or LineString) is within the second GeoJSON object (Point, Polygon, or LineString).
ST_INTERSECTS (spatial_expr, spatial_expr)ST_INTERSECTS (spatial_expr, spatial_expr) 返回一个布尔表达式,指示两个指定的 GeoJSON 对象(PointPolygonLineString)是否相交。Returns a Boolean expression indicating whether the two specified GeoJSON objects (Point, Polygon, or LineString) intersect.
ST_ISVALIDST_ISVALID 返回一个布尔值,指示指定的 GeoJSON PointPolygonLineString 表达式是否有效。Returns a Boolean value indicating whether the specified GeoJSON Point, Polygon, or LineString expression is valid.
ST_ISVALIDDETAILEDST_ISVALIDDETAILED 如果指定的 GeoJSON Point 或、PolygonLineString 表达式有效,则返回包含布尔值的 JSON 值;如果无效,则返回字符串值形式的原因。Returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid, and if invalid, the reason as a string value.

可以使用空间函数对空间数据执行邻近查询。You can use spatial functions to perform proximity queries against spatial data. 例如,以下查询使用 ST_DISTANCE 内置函数返回所有家庭项,且这些文档在指定位置的 30 公里内:For example, here's a query that returns all family items that are within 30 km of a specified location using the ST_DISTANCE built-in function:

    SELECT f.id
    FROM Families f
    WHERE ST_DISTANCE(f.location, {'type': 'Point', 'coordinates':[31.9, -4.8]}) < 30000

结果为:The result is:

    [{
      "id": "WakefieldFamily"
    }]

有关 Cosmos DB 中地理支持的详细信息,请参阅在 Azure Cosmos DB 中使用地理数据For more information on geospatial support in Cosmos DB, see Working with geospatial data in Azure Cosmos DB.

参数化查询Parameterized queries

Cosmos DB 支持使用带有常用 @ 表示法的参数进行查询。Cosmos DB supports queries with parameters expressed by the familiar @ notation. 参数化 SQL 为用户输入提供可靠的处理和转义,可防止通过 SQL 注入发生意外的数据泄露。Parameterized SQL provides robust handling and escaping of user input, and prevents accidental exposure of data through SQL injection.

例如,可以编写一个将 lastNameaddress.state 用作参数的查询,并根据用户输入针对 lastNameaddress.state 的各种值执行此查询。For example, you can write a query that takes lastName and address.state as parameters, and execute it for various values of lastName and address.state based on user input.

    SELECT *
    FROM Families f
    WHERE f.lastName = @lastName AND f.address.state = @addressState

然后,可将此请求作为参数化 JSON 查询发送到 Cosmos DB,如下所示:You can then send this request to Cosmos DB as a parameterized JSON query like the following:

    {
        "query": "SELECT * FROM Families f WHERE f.lastName = @lastName AND f.address.state = @addressState",
        "parameters": [
            {"name": "@lastName", "value": "Wakefield"},
            {"name": "@addressState", "value": "NY"},
        ]
    }

以下示例使用参数化查询设置 TOP 参数:The following example sets the TOP argument with a parameterized query:

    {
        "query": "SELECT TOP @n * FROM Families",
        "parameters": [
            {"name": "@n", "value": 10},
        ]
    }

参数值可以是任何有效的 JSON:字符串、数字、布尔值、null,甚至数组或嵌套的 JSON。Parameter values can be any valid JSON: strings, numbers, Booleans, null, even arrays or nested JSON. 由于 Cosmos DB 是无架构的,因此不会针对任何类型验证参数。Since Cosmos DB is schemaless, parameters aren't validated against any type.

JavaScript 集成JavaScript integration

Azure Cosmos DB 提供一种编程模型,用于通过存储过程和触发器对容器直接执行基于 JavaScript 的应用程序逻辑。Azure Cosmos DB provides a programming model for executing JavaScript-based application logic directly on containers, using stored procedures and triggers. 此模型支持:This model supports:

  • 通过在数据库引擎中深度集成 JavaScript 运行时,对容器中的项执行高性能事务性 CRUD 操作和查询。High-performance transactional CRUD operations and queries against items in a container, by virtue of the deep integration of the JavaScript runtime within the database engine.
  • 控制流、变量范围和分配的自然建模和将异常处理基元与数据库事务集成。A natural modeling of control flow, variable scoping, and assignment and integration of exception-handling primitives with database transactions.

有关 Azure Cosmos DB JavaScript 集成的详细信息,请参阅 JavaScript 服务器端 API 部分。For more information about Azure Cosmos DB JavaScript integration, see the JavaScript server-side API section.

运算符评估Operator evaluation

Cosmos DB 是一个 JSON 数据库,在 JavaScript 运算符和评估语义方面具有许多相似之处。Cosmos DB, by virtue of being a JSON database, draws parallels with JavaScript operators and evaluation semantics. 就 JSON 支持而言,Cosmos DB 会尽量保留 JavaScript 语义,但运算求值在某些情况下会有所不同。Cosmos DB tries to preserve JavaScript semantics in terms of JSON support, but the operation evaluation deviates in some instances.

与传统 SQL 不同,在 SQL API 中,在该 API 从数据库检索出值之前,值的类型往往是未知的。In the SQL API, unlike in traditional SQL, the types of values are often not known until the API retrieves the values from the database. 为了高效执行查询,大多数运算符具有严格的类型要求。In order to efficiently execute queries, most of the operators have strict type requirements.

与 JavaScript 不同,SQL API 不会执行隐式转换。Unlike JavaScript, the SQL API doesn't perform implicit conversions. 例如,类似于 SELECT * FROM Person p WHERE p.Age = 21 的查询会匹配包含 21 值的 Age 属性的项。For instance, a query like SELECT * FROM Person p WHERE p.Age = 21 matches items that contain an Age property whose value is 21. 它不会匹配其 Age 属性可能与 twenty-one02121.0 等无限变体匹配的任何其他项。It doesn't match any other item whose Age property matches possibly infinite variations like twenty-one, 021, or 21.0. 这与 JavaScript 相反,在 JavaScript 中,字符串值会根据 == 等运算符隐式转换为数字。This contrasts with JavaScript, where string values are implicitly cast to numbers based on operator, for example: ==. 此 SQL API 行为对于有效的索引匹配至关重要。This SQL API behavior is crucial for efficient index matching.

SQL 查询执行SQL query execution

能够发出 HTTP/HTTPS 请求的任何语言都可以调用 Cosmos DB REST API。Any language capable of making HTTP/HTTPS requests can call the Cosmos DB REST API. Cosmos DB 还为 .NET、Node.js、JavaScript 和 Python 编程语言提供编程库。Cosmos DB also offers programming libraries for .NET, Node.js, JavaScript, and Python programming languages. REST API 和库全部支持通过 SQL 执行的查询,.NET SDK 还支持 LINQ 查询The REST API and libraries all support querying through SQL, and the .NET SDK also supports LINQ querying.

以下示例演示了如何对 Cosmos DB 数据库帐户创建和提交该查询。The following examples show how to create a query and submit it against a Cosmos DB database account.

REST APIREST API

Cosmos DB 通过 HTTP 提供开放的 RESTful 编程模型。Cosmos DB offers an open RESTful programming model over HTTP. 资源模型由 Azure 订阅预配的数据库帐户下的一组资源组成。The resource model consists of a set of resources under a database account, which an Azure subscription provisions. 该数据库帐户由一组数据库组成,其中的每个数据库可以包含多个容器,而每个容器又可以包含项、UDF 和其他资源类型。The database account consists of a set of databases, each of which can contain multiple containers, which in turn contain items, UDFs, and other resource types. 可以使用稳定的逻辑 URI 对每个 Cosmos DB 资源进行寻址。Each Cosmos DB resource is addressable using a logical and stable URI. 一组资源称作一个源。A set of resources is called a feed.

这些资源的基本交互模型是通过 HTTP 谓词 GETPUTPOSTDELETE 及其标准解释实现的。The basic interaction model with these resources is through the HTTP verbs GET, PUT, POST, and DELETE, with their standard interpretations. 使用 POST 可以创建新的资源、执行存储过程或发出 Cosmos DB 查询。Use POST to create a new resource, execute a stored procedure, or issue a Cosmos DB query. 查询始终为只读操作,且无任何副作用。Queries are always read-only operations with no side-effects.

以下示例演示了如何根据示例项针对 SQL API 查询发出 POSTThe following examples show a POST for a SQL API query against the sample items. 该查询对 JSON name 属性应用一个简单的筛选器。The query has a simple filter on the JSON name property. x-ms-documentdb-isquery 和 Content-Type: application/query+json 标头表示该操作是一个查询。The x-ms-documentdb-isquery and Content-Type: application/query+json headers denote that the operation is a query. 请将 mysqlapicosmosdb.documents.azure.cn:443 替换为 Cosmos DB 帐户的 URI。Replace mysqlapicosmosdb.documents.azure.cn:443 with the URI for your Cosmos DB account.

    POST https://mysqlapicosmosdb.documents.azure.cn:443/docs HTTP/1.1
    ...
    x-ms-documentdb-isquery: True
    Content-Type: application/query+json

    {
        "query": "SELECT * FROM Families f WHERE f.id = @familyId",
        "parameters": [
            {"name": "@familyId", "value": "AndersenFamily"}
        ]
    }

其结果是:The results are:

    HTTP/1.1 200 Ok
    x-ms-activity-id: 8b4678fa-a947-47d3-8dd3-549a40da6eed
    x-ms-item-count: 1
    x-ms-request-charge: 0.32

    {  
       "_rid":"u1NXANcKogE=",
       "Documents":[  
          {  
             "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"
             },
             "_rid":"u1NXANcKogEcAAAAAAAAAA==",
             "_ts":1407691744,
             "_self":"dbs\/u1NXAA==\/colls\/u1NXANcKogE=\/docs\/u1NXANcKogEcAAAAAAAAAA==\/",
             "_etag":"00002b00-0000-0000-0000-53e7abe00000",
             "_attachments":"_attachments\/"
          }
       ],
       "count":1
    }

下面这个更复杂的查询从某个联接操作返回多个结果:The next, more complex query returns multiple results from a join:

    POST https://https://mysqlapicosmosdb.documents.azure.cn:443/docs HTTP/1.1
    ...
    x-ms-documentdb-isquery: True
    Content-Type: application/query+json

    {
        "query": "SELECT
                     f.id AS familyName,
                     c.givenName AS childGivenName,
                     c.firstName AS childFirstName,
                     p.givenName AS petName
                  FROM Families f
                  JOIN c IN f.children
                  JOIN p in c.pets",
        "parameters": [] 
    }

其结果是:The results are:

    HTTP/1.1 200 Ok
    x-ms-activity-id: 568f34e3-5695-44d3-9b7d-62f8b83e509d
    x-ms-item-count: 1
    x-ms-request-charge: 7.84

    {  
       "_rid":"u1NXANcKogE=",
       "Documents":[  
          {  
             "familyName":"AndersenFamily",
             "childFirstName":"Henriette Thaulow",
             "petName":"Fluffy"
          },
          {  
             "familyName":"WakefieldFamily",
             "childGivenName":"Jesse",
             "petName":"Goofy"
          },
          {  
             "familyName":"WakefieldFamily",
             "childGivenName":"Jesse",
             "petName":"Shadow"
          }
       ],
       "count":3
    }

如果查询的结果无法包含在一页中,则 REST API 会通过 x-ms-continuation-token 响应标头返回继续标记。If a query's results can't fit in a single page, the REST API returns a continuation token through the x-ms-continuation-token response header. 客户端可以通过在后续结果中包含该标头对结果进行分页。Clients can paginate results by including the header in the subsequent results. 也可以通过 x-ms-max-item-count 数字标头控制每页的结果数。You can also control the number of results per page through the x-ms-max-item-count number header.

如果查询包含 COUNT 等聚合函数,则查询页可以仅通过一个结果页返回部分聚合的值。If a query has an aggregation function like COUNT, the query page may return a partially aggregated value over only one page of results. 客户端必须对这些结果执行二级聚合才能生成最终结果。Clients must perform a second-level aggregation over these results to produce the final results. 例如,将各个页中返回的计数求和可以返回总计数。For example, sum over the counts returned in the individual pages to return the total count.

若要管理查询的数据一致性策略,请使用 x-ms-consistency-level 标头(如所有的 REST API 请求)。To manage the data consistency policy for queries, use the x-ms-consistency-level header as in all REST API requests. 对于会话一致性,还需要回显查询请求中最新的 x-ms-session-token Cookie 标头。Session consistency also requires echoing the latest x-ms-session-token cookie header in the query request. 查询容器的索引策略也可以影响查询结果的一致性。The queried container's indexing policy can also influence the consistency of query results. 使用默认的索引策略设置,容器的索引始终与项内容保持同步,且查询结果将与为数据选择的一致性匹配。With the default indexing policy settings for containers, the index is always current with the item contents, and query results match the consistency chosen for data. 有关详细信息,请参阅 Azure Cosmos DB 一致性级别For more information, see Azure Cosmos DB consistency levels.

如果容器上配置的索引策略不能支持指定的查询,Azure Cosmos DB 服务器会返回 400“错误的请求”。If the configured indexing policy on the container can't support the specified query, the Azure Cosmos DB server returns 400 "Bad Request". 使用从索引中显式排除的路径执行查询时,将返回此错误消息。This error message returns for queries with paths explicitly excluded from indexing. 可以指定 x-ms-documentdb-query-enable-scan 标头,以便在索引不可用时允许查询执行扫描。You can specify the x-ms-documentdb-query-enable-scan header to allow the query to perform a scan when an index isn't available.

可以通过将 x-ms-documentdb-populatequerymetrics 标头设置为 true 来获取有关查询执行的详细指标。You can get detailed metrics on query execution by setting the x-ms-documentdb-populatequerymetrics header to true. 有关详细信息,请参阅 Azure Cosmos DB 的 SQL 查询指标For more information, see SQL query metrics for Azure Cosmos DB.

C# (.NET SDK)C# (.NET SDK)

.NET SDK 支持 LINQ 和 SQL 查询。The .NET SDK supports both LINQ and SQL querying. 以下示例演示如何使用 .NET 执行前面所示的筛选查询:The following example shows how to perform the preceding filter query with .NET:

    foreach (var family in client.CreateDocumentQuery(containerLink,
        "SELECT * FROM Families f WHERE f.id = \"AndersenFamily\""))
    {
        Console.WriteLine("\tRead {0} from SQL", family);
    }

    SqlQuerySpec query = new SqlQuerySpec("SELECT * FROM Families f WHERE f.id = @familyId");
    query.Parameters = new SqlParameterCollection();
    query.Parameters.Add(new SqlParameter("@familyId", "AndersenFamily"));

    foreach (var family in client.CreateDocumentQuery(containerLink, query))
    {
        Console.WriteLine("\tRead {0} from parameterized SQL", family);
    }

    foreach (var family in (
        from f in client.CreateDocumentQuery(containerLink)
        where f.Id == "AndersenFamily"
        select f))
    {
        Console.WriteLine("\tRead {0} from LINQ query", family);
    }

    foreach (var family in client.CreateDocumentQuery(containerLink)
        .Where(f => f.Id == "AndersenFamily")
        .Select(f => f))
    {
        Console.WriteLine("\tRead {0} from LINQ lambda", family);
    }

以下示例比较了每个项内等式的两个属性,并使用匿名投影。The following example compares two properties for equality within each item, and uses anonymous projections.

    foreach (var family in client.CreateDocumentQuery(containerLink,
        @"SELECT {""Name"": f.id, ""City"":f.address.city} AS Family
        FROM Families f
        WHERE f.address.city = f.address.state"))
    {
        Console.WriteLine("\tRead {0} from SQL", family);
    }

    foreach (var family in (
        from f in client.CreateDocumentQuery<Family>(containerLink)
        where f.address.city == f.address.state
        select new { Name = f.Id, City = f.address.city }))
    {
        Console.WriteLine("\tRead {0} from LINQ query", family);
    }

    foreach (var family in
        client.CreateDocumentQuery<Family>(containerLink)
        .Where(f => f.address.city == f.address.state)
        .Select(f => new { Name = f.Id, City = f.address.city }))
    {
        Console.WriteLine("\tRead {0} from LINQ lambda", family);
    }

以下示例演示通过 LINQ SelectMany 表达的联接。The next example shows joins, expressed through LINQ SelectMany.

    foreach (var pet in client.CreateDocumentQuery(containerLink,
          @"SELECT p
            FROM Families f
                 JOIN c IN f.children
                 JOIN p in c.pets
            WHERE p.givenName = ""Shadow"""))
    {
        Console.WriteLine("\tRead {0} from SQL", pet);
    }

    // Equivalent in Lambda expressions:
    foreach (var pet in
        client.CreateDocumentQuery<Family>(containerLink)
        .SelectMany(f => f.children)
        .SelectMany(c => c.pets)
        .Where(p => p.givenName == "Shadow"))
    {
        Console.WriteLine("\tRead {0} from LINQ lambda", pet);
    }

.NET 客户端自动循环访问 foreach 块中所有的查询结果页,如前面的示例中所示。The .NET client automatically iterates through all the pages of query results in the foreach blocks, as shown in the preceding example. REST API 部分介绍的查询选项也适用于在 CreateDocumentQuery 方法中使用 FeedOptionsFeedResponse 类的 .NET SDK。The query options introduced in the REST API section are also available in the .NET SDK, using the FeedOptions and FeedResponse classes in the CreateDocumentQuery method. 可以使用 MaxItemCount 设置控制页数。You can control the number of pages by using the MaxItemCount setting.

还可以通过使用 IQueryable 对象创建 IDocumentQueryable,并读取 ResponseContinuationToken 值并将它们作为 FeedOptions 中的 RequestContinuationToken 向回传递,从而显式控制分页。You can also explicitly control paging by creating IDocumentQueryable using the IQueryable object, then by reading the ResponseContinuationToken values and passing them back as RequestContinuationToken in FeedOptions. 可以设置 EnableScanInQuery,以便在配置的索引策略不支持该查询时启用扫描。You can set EnableScanInQuery to enable scans when the query isn't supported by the configured indexing policy. 对于分区容器,可以使用 PartitionKey 针对单个分区运行查询,不过,Azure Cosmos DB 可以自动从查询文本中提取此信息。For partitioned containers, you can use PartitionKey to run the query against a single partition, although Azure Cosmos DB can automatically extract this from the query text. 可以使用 EnableCrossPartitionQuery 针对多个分区运行查询。You can use EnableCrossPartitionQuery to run queries against multiple partitions.

有关更多包含查询的 .NET 示例,请参阅 GitHub 中的 Azure Cosmos DB .NET 示例For more .NET samples with queries, see the Azure Cosmos DB .NET samples in GitHub.

JavaScript 服务器端 APIJavaScript server-side API

Cosmos DB 提供一种编程模型,用于通过存储过程和触发器对容器直接执行基于 JavaScript 的应用程序逻辑。Cosmos DB provides a programming model for executing JavaScript based application logic directly on containers, using stored procedures and triggers. 然后,在容器级别注册的 JavaScript 逻辑可以针对给定容器中的、包装在环境 ACID 事务中的项发出数据库操作。The JavaScript logic registered at the container level can then issue database operations on the items of the given container, wrapped in ambient ACID transactions.

以下示例演示了如何在 JavaScript 服务器 API 中使用 queryDocuments 从存储过程和触发器内部进行查询。The following example shows how to use queryDocuments in the JavaScript server API to make queries from inside stored procedures and triggers:

    function findName(givenName, familyName) {
        var context = getContext();
        var containerManager = context.getCollection();
        var containerLink = containerManager.getSelfLink()

        // create a new item.
        containerManager.createDocument(containerLink,
            { givenName: givenName, familyName: familyName },
            function (err, documentCreated) {
                if (err) throw new Error(err.message);

                // filter items by familyName
                var filterQuery = "SELECT * from root r WHERE r.familyName = 'Wakefield'";
                containerManager.queryDocuments(containerLink,
                    filterQuery,
                    function (err, matchingDocuments) {
                        if (err) throw new Error(err.message);
    context.getResponse().setBody(matchingDocuments.length);

                        // Replace the familyName for all items that satisfied the query.
                        for (var i = 0; i < matchingDocuments.length; i++) {
                            matchingDocuments[i].familyName = "Robin Wakefield";
                            // we don't need to execute a callback because they are in parallel
                            containerManager.replaceDocument(matchingDocuments[i]._self,
                                matchingDocuments[i]);
                        }
                    })
            });
    }

LINQ to SQL APILINQ to SQL API

LINQ 是一个 .NET 编程模型,它将计算表达为对对象流的查询。LINQ is a .NET programming model that expresses computation as queries on object streams. Cosmos DB 提供一个客户端库,通过促进 JSON 与 .NET 对象之间的转换,以及从 LINQ 查询的子集到 Cosmos DB 查询的映射,来与 LINQ 进行交互。Cosmos DB provides a client-side library to interface with LINQ by facilitating a conversion between JSON and .NET objects and a mapping from a subset of LINQ queries to Cosmos DB queries.

下图演示了使用 Cosmos DB 的支持性 LINQ 查询的体系结构。The following diagram shows the architecture of supporting LINQ queries using Cosmos DB. 使用 Cosmos DB 客户端,可以创建直接查询 Cosmos DB 查询提供程序的 IQueryable 对象,并将 LINQ 查询转换为 Cosmos DB 查询。Using the Cosmos DB client, you can create an IQueryable object that directly queries the Cosmos DB query provider, and translates the LINQ query into a Cosmos DB query. 然后,将该查询传递到 Cosmos DB 服务器,后者会检索一组 JSON 格式的结果。You then pass the query to the Cosmos DB server, which retrieves a set of results in JSON format. 在客户端中,JSON 反序列化程序会将结果转换为 .NET 对象流。The JSON deserializer converts the results into a stream of .NET objects on the client side.

支持使用 SQL API 的 LINQ 查询的体系结构 - SQL 语法、JSON 查询语言、数据库概念和 SQL 查询

.NET 和 JSON 映射.NET and JSON mapping

.NET 对象与 JSON 项之间的映射是自然的。The mapping between .NET objects and JSON items is natural. 每个数据成员字段映射到 JSON 对象,其中的字段名称映射到对象的 key 部分,值以递归方式映射到该对象的 value 部分。Each data member field maps to a JSON object, where the field name maps to the key part of the object, and the value recursively maps to the value part of the object. 以下代码将 Family 类映射到 JSON 项,然后创建 Family 对象:The following code maps the Family class to a JSON item, and then creates a Family object:

    public class Family
    {
        [JsonProperty(PropertyName="id")]
        public string Id;
        public Parent[] parents;
        public Child[] children;
        public bool isRegistered;
    };

    public struct Parent
    {
        public string familyName;
        public string givenName;
    };

    public class Child
    {
        public string familyName;
        public string givenName;
        public string gender;
        public int grade;
        public List<Pet> pets;
    };

    public class Pet
    {
        public string givenName;
    };

    public class Address
    {
        public string state;
        public string county;
        public string city;
    };

    // Create a Family object.
    Parent mother = new Parent { familyName= "Wakefield", givenName="Robin" };
    Parent father = new Parent { familyName = "Miller", givenName = "Ben" };
    Child child = new Child { familyName="Merriam", givenName="Jesse", gender="female", grade=1 };
    Pet pet = new Pet { givenName = "Fluffy" };
    Address address = new Address { state = "NY", county = "Manhattan", city = "NY" };
    Family family = new Family { Id = "WakefieldFamily", parents = new Parent [] { mother, father}, children = new Child[] { child }, isRegistered = false };

以上示例将创建以下 JSON 项:The preceding example creates the following JSON item:

    {
        "id": "WakefieldFamily",
        "parents": [
            { "familyName": "Wakefield", "givenName": "Robin" },
            { "familyName": "Miller", "givenName": "Ben" }
        ],
        "children": [
            {
                "familyName": "Merriam",
                "givenName": "Jesse",
                "gender": "female",
                "grade": 1,
                "pets": [
                    { "givenName": "Goofy" },
                    { "givenName": "Shadow" }
                ]
            },
            { 
              "familyName": "Miller",
              "givenName": "Lisa",
              "gender": "female",
              "grade": 8
            }
        ],
        "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
        "isRegistered": false
    };

LINQ 到 SQL 转换LINQ to SQL translation

Cosmos DB 查询提供程序执行从 LINQ 查询到 Cosmos DB SQL 查询的最有效映射。The Cosmos DB query provider performs a best effort mapping from a LINQ query into a Cosmos DB SQL query. 以下内容假设你对 LINQ 有一个基本的了解。The following description assumes a basic familiarity with LINQ.

查询提供程序类型系统仅支持 JSON 基元类型:数字、布尔值、字符串和 null。The query provider type system supports only the JSON primitive types: numeric, Boolean, string, and null.

查询提供程序支持以下标量表达式:The query provider supports the following scalar expressions:

  • 常量值,包括评估查询时基元数据类型的常量值。Constant values, including constant values of the primitive data types at query evaluation time.

  • 引用对象或数组元素的属性的属性/数组索引表达式。Property/array index expressions that refer to the property of an object or an array element. 例如:For example:

    family.Id;
    family.children[0].familyName;
    family.children[0].grade;
    family.children[n].grade; //n is an int variable
    
  • 算术表达式,包括针对数值和布尔值运行的常见算术表达式。Arithmetic expressions, including common arithmetic expressions on numerical and Boolean values. 有关完整列表,请参阅 Azure Cosmos DB SQL 规范For the complete list, see the Azure Cosmos DB SQL specification.

    2 * family.children[0].grade;
    x + y;
    
  • 字符串比较表达式,包括将字符串值与某些常量字符串值进行比较。String comparison expressions, which include comparing a string value to some constant string value.

    mother.familyName == "Wakefield";
    child.givenName == s; //s is a string variable
    
  • 对象/数组创建表达式,返回复合值类型或匿名类型的对象,或此类对象组成的数组。Object/array creation expressions, which return an object of compound value type or anonymous type, or an array of such objects. 可以嵌套这些值。You can nest these values.

    new Parent { familyName = "Wakefield", givenName = "Robin" };
    new { first = 1, second = 2 }; //an anonymous type with two fields  
    new int[] { 3, child.grade, 5 };
    

支持的 LINQ 运算符Supported LINQ operators

SQL .NET SDK 随附的 LINQ 提供程序支持以下运算符:The LINQ provider included with the SQL .NET SDK supports the following operators:

  • Select:投影转换为 SQL SELECT(包括对象构造)。Select: Projections translate to SQL SELECT, including object construction.
  • Where:筛选器转换为 SQL WHERE,支持 &&||! 到 SQL 运算符的转换Where: Filters translate to SQL WHERE, and support translation between &&, ||, and ! to the SQL operators
  • SelectMany:允许将数组展开到 SQL JOIN 子句。SelectMany: Allows unwinding of arrays to the SQL JOIN clause. 用于将表达式链接或嵌套到对数组元素应用的筛选器。Use to chain or nest expressions to filter on array elements.
  • OrderByOrderByDescending:使用 ASC 或 DESC 转换为 ORDER BY。OrderBy and OrderByDescending: Translate to ORDER BY with ASC or DESC.
  • 用于聚合的 CountSumMinMaxAverage 运算符及其异步等效项 CountAsyncSumAsyncMinAsyncMaxAsyncAverageAsyncCount, Sum, Min, Max, and Average operators for aggregation, and their async equivalents CountAsync, SumAsync, MinAsync, MaxAsync, and AverageAsync.
  • CompareTo:转换为范围比较。CompareTo: Translates to range comparisons. 通常用于字符串,因为它们在 .NET 中不可进行比较。Commonly used for strings, since they're not comparable in .NET.
  • Take:转换为 SQL TOP,用于限制查询中的结果。Take: Translates to SQL TOP for limiting results from a query.
  • 数学函数:支持从 .NET AbsAcosAsinAtanCeilingCosExpFloorLogLog10PowRoundSignSinSqrtTanTruncate 转换为等效的 SQL 内置函数。Math functions: Supports translation from .NET Abs, Acos, Asin, Atan, Ceiling, Cos, Exp, Floor, Log, Log10, Pow, Round, Sign, Sin, Sqrt, Tan, and Truncate to the equivalent SQL built-in functions.
  • 字符串函数:支持从.NET ConcatContainsCountEndsWithIndexOfReplaceReverseStartsWithSubStringToLowerToUpperTrimEndTrimStart 转换为等效的 SQL 内置函数。String functions: Supports translation from .NET Concat, Contains, Count, EndsWith,IndexOf, Replace, Reverse, StartsWith, SubString, ToLower, ToUpper, TrimEnd, and TrimStart to the equivalent SQL built-in functions.
  • 数组函数:支持从 .NET ConcatContainsCount 转换为等效的 SQL 内置函数。Array functions: Supports translation from .NET Concat, Contains, and Count to the equivalent SQL built-in functions.
  • 地理空间扩展函数:支持从存根方法 DistanceIsValidIsValidDetailedWithin 转换为等效的 SQL 内置函数。Geospatial Extension functions: Supports translation from stub methods Distance, IsValid, IsValidDetailed, and Within to the equivalent SQL built-in functions.
  • 用户定义的函数扩展函数:支持从存根方法 UserDefinedFunctionProvider.Invoke 转换为相应的用户定义的函数。User-Defined Function Extension function: Supports translation from the stub method UserDefinedFunctionProvider.Invoke to the corresponding user-defined function.
  • 其他:支持 Coalesce 和条件运算符的转换。Miscellaneous: Supports translation of Coalesce and conditional operators. 可以根据上下文将 Contains 转换为字符串 CONTAINS、ARRAY_CONTAINS 或 SQL IN。Can translate Contains to String CONTAINS, ARRAY_CONTAINS, or SQL IN, depending on context.

SQL 查询运算符SQL query operators

以下示例演示了一些标准 LINQ 查询运算符如何转换为 Cosmos DB 查询。The following examples illustrate how some of the standard LINQ query operators translate to Cosmos DB queries.

Select 运算符Select operator

语法为 input.Select(x => f(x)),其中 f 是一个标量表达式。The syntax is input.Select(x => f(x)), where f is a scalar expression.

Select 运算符,示例 1:Select operator, example 1:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Select(family => family.parents[0].familyName);
    
  • SQLSQL

    SELECT VALUE f.parents[0].familyName
    FROM Families f
    

Select 运算符,示例 2:Select operator, example 2:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Select(family => family.children[0].grade + c); // c is an int variable
    
  • SQLSQL

    SELECT VALUE f.children[0].grade + c
    FROM Families f
    

Select 运算符,示例 3:Select operator, example 3:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Select(family => new
    {
        name = family.children[0].familyName,
        grade = family.children[0].grade + 3
    });
    
  • SQLSQL

    SELECT VALUE {"name":f.children[0].familyName,
                "grade": f.children[0].grade + 3 }
    FROM Families f
    

SelectMany 运算符SelectMany operator

语法为 input.SelectMany(x => f(x)),其中 f 是返回容器类型的标量表达式。The syntax is input.SelectMany(x => f(x)), where f is a scalar expression that returns a container type.

  • LINQ Lambda 表达式LINQ lambda expression

    input.SelectMany(family => family.children);
    
  • SQLSQL

    SELECT VALUE child
    FROM child IN Families.children
    

Where 运算符Where operator

语法为 input.Where(x => f(x)),其中 f 是返回布尔值的标量表达式。The syntax is input.Where(x => f(x)), where f is a scalar expression, which returns a Boolean value.

Where 运算符,示例 1:Where operator, example 1:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Where(family=> family.parents[0].familyName == "Wakefield");
    
  • SQLSQL

    SELECT *
    FROM Families f
    WHERE f.parents[0].familyName = "Wakefield"
    

Where 运算符,示例 2:Where operator, example 2:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Where(
      family => family.parents[0].familyName == "Wakefield" &&
      family.children[0].grade < 3);
    
  • SQLSQL

    SELECT *
    FROM Families f
    WHERE f.parents[0].familyName = "Wakefield"
    AND f.children[0].grade < 3
    

复合 SQL 查询Composite SQL queries

将上述运算符组合到一起可以构成更强大的查询。You can compose the preceding operators to form more powerful queries. 由于 Cosmos DB 支持嵌套的容器,因此你可以连接或嵌套这种组合。Since Cosmos DB supports nested containers, you can concatenate or nest the composition.

串联Concatenation

语法为 input(.|.SelectMany())(.Select()|.Where())*The syntax is input(.|.SelectMany())(.Select()|.Where())*. 连接的查询可以使用可选的 SelectMany 查询开头,后接多个 SelectWhere 运算符。A concatenated query can start with an optional SelectMany query, followed by multiple Select or Where operators.

连接,示例 1:Concatenation, example 1:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Select(family=>family.parents[0])
      .Where(familyName == "Wakefield");
    
  • SQLSQL

    SELECT *
    FROM Families f
    WHERE f.parents[0].familyName = "Wakefield"
    

连接,示例 2:Concatenation, example 2:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Where(family => family.children[0].grade > 3)
      .Select(family => family.parents[0].familyName);
    
  • SQLSQL

    SELECT VALUE f.parents[0].familyName
    FROM Families f
    WHERE f.children[0].grade > 3
    

连接,示例 3:Concatenation, example 3:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Select(family => new { grade=family.children[0].grade}).
      Where(anon=> anon.grade < 3);
    
  • SQLSQL

    SELECT *
    FROM Families f
    WHERE ({grade: f.children[0].grade}.grade > 3)
    

连接,示例 4:Concatenation, example 4:

  • LINQ Lambda 表达式LINQ lambda expression

    input.SelectMany(family => family.parents)
      .Where(parent => parents.familyName == "Wakefield");
    
  • SQLSQL

    SELECT *
    FROM p IN Families.parents
    WHERE p.familyName = "Wakefield"
    

嵌套Nesting

语法为 input.SelectMany(x=>x.Q()),其中 QSelectSelectManyWhere 运算符。The syntax is input.SelectMany(x=>x.Q()) where Q is a Select, SelectMany, or Where operator.

嵌套查询会将内部查询应用到外部容器的每个元素。A nested query applies the inner query to each element of the outer container. 一个重要的功能是内部查询可以引用外部容器(如自联接)中元素的字段。One important feature is that the inner query can refer to the fields of the elements in the outer container, like a self-join.

嵌套,示例 1:Nesting, example 1:

  • LINQ Lambda 表达式LINQ lambda expression

    input.SelectMany(family=>
      family.parents.Select(p => p.familyName));
    
  • SQLSQL

    SELECT VALUE p.familyName
    FROM Families f
    JOIN p IN f.parents
    

嵌套,示例 2:Nesting, example 2:

  • LINQ Lambda 表达式LINQ lambda expression

    input.SelectMany(family =>
      family.children.Where(child => child.familyName == "Jeff"));
    
  • SQLSQL

    SELECT *
    FROM Families f
    JOIN c IN f.children
    WHERE c.familyName = "Jeff"
    

嵌套,示例 3:Nesting, example 3:

  • LINQ Lambda 表达式LINQ lambda expression

    input.SelectMany(family => family.children.Where(
      child => child.familyName == family.parents[0].familyName));
    
  • SQLSQL

    SELECT *
    FROM Families f
    JOIN c IN f.children
    WHERE c.familyName = f.parents[0].familyName

参考References

  • Azure Cosmos DB SQL 规范Azure Cosmos DB SQL specification
  • ANSI SQL 2011ANSI SQL 2011
  • JSONJSON
  • Javascript 规范Javascript Specification
  • LINQLINQ
  • Graefe, Goetz。Graefe, Goetz. Query evaluation techniques for large databases(适用于大型数据库的查询评估技术)Query evaluation techniques for large databases. 《ACM 计算调查》第 25 期ACM Computing Surveys 25, no. 第 2 卷 (1993)。2 (1993).
  • Graefe, G。“用于优化查询的 Cascades 框架”。Graefe, G. "The Cascades framework for query optimization." 《IEEE 数据工程期刊》IEEE Data Eng. Bull. 第 18 期18, no. 第 3 卷 (1995)。3 (1995).
  • Lu、Ooi、Tan。Lu, Ooi, Tan. “并行关系数据库系统中的查询处理”。"Query Processing in Parallel Relational Database Systems." 《IEEE 计算机协会期刊》(1994)。IEEE Computer Society Press (1994).
  • Olston、Christopher、Benjamin Reed、Utkarsh Srivastava、Ravi Kumar 和 Andrew Tomkins。Olston, Christopher, Benjamin Reed, Utkarsh Srivastava, Ravi Kumar, and Andrew Tomkins. “Pig Latin:并不是很难懂的数据处理语言”。"Pig Latin: A Not-So-Foreign Language for Data Processing." SIGMOD (2008)。SIGMOD (2008).

后续步骤Next steps