Documentdb Sql 简明教程
DocumentDB SQL - Overview
DocumentDB 是 Microsoft 最新 NoSQL 文档数据库平台，它在 Azure 上运行。在本教程中，我们将了解使用 DocumentDB 支持的特殊版本的 SQL 查询文档的全部内容。
DocumentDB is Microsoft’s newest NoSQL document database platform that runs on Azure. In this tutorial, we will learn all about querying documents using the special version of SQL supported by DocumentDB.
NoSQL Document Database
DocumentDB 是 Microsoft 最新 NoSQL 文档数据库，但是，当我们说 NoSQL 文档数据库时，NoSQL 和文档数据库具体是什么意思？
DocumentDB is Microsoft’s newest NoSQL document database, however, when we say NoSQL document database, what precisely do we mean by NoSQL, and document database?
-
SQL means Structured Query Language which is a traditional query language of relational databases. SQL is often equated with relational databases.
-
It is really more helpful to think of a NoSQL database as a non-relational database, so NoSQL really means non-relational.
有不同类型的 NoSQL 数据库，包括键值存储，例如：
There are different types of NoSQL databases which include key value stores such as −
-
Azure Table Storage
-
Column-based stores, like Cassandra
-
Graph databases, like NEO4
-
Document databases, like MongoDB and Azure DocumentDB
Why SQL Syntax?
这乍听起来可能很奇怪，但在 DocumentDB 中将其称为 NoSQL 数据库时，我们使用 SQL 进行查询。如上所述，这是植根于 JSON 和 JavaScript 语义的特殊版本的 SQL。
This can sound strange at first, but in DocumentDB which is a NoSQL database, we query using SQL. As mentioned above, this is a special version of SQL rooted in JSON and JavaScript semantics.
-
SQL is just a language, but it’s also a very popular language that’s rich and expressive. Thus, it definitely seems like a good idea to use some dialect of SQL rather than come up with a whole new way of expressing queries that we would need to learn if you wanted to get documents out of your database.
-
SQL is designed for relational databases, and DocumentDB is a non-relational document database. DocumentDB team has actually adapted the SQL syntax for the non-relational world of document databases, and this is what is meant by rooting SQL in JSON and JavaScript.
-
The language still reads as familiar SQL, but the semantics are all based on schemafree JSON documents rather than relational tables. In DocumentDB, we will be working with JavaScript data types rather than SQL data types. We will be familiar with SELECT, FROM, WHERE, and so on, but with JavaScript types, which are limited to numbers and strings, objects, arrays, Boolean, and null are far fewer than the wide range of SQL data types.
-
Similarly, expressions are evaluated as JavaScript expressions rather than some form of T-SQL. For example, in a world of denormalized data, we’re not dealing with the rows and columns, but schema-free documents with hierarchal structures that contain nested arrays and objects.
How does SQL Work?
DocumentDB 团队已经通过多种创新方式回答了这个问题。如下列出了其中的少数几种 −
The DocumentDB team has answered this question in several innovative ways. Few of them are listed as follows −
-
First, assuming you’ve not changed the default behavior to automatically index every property in a document, you can use dotted notation in your queries to navigate a path to any property no matter how deeply nested it may be within the document.
-
You can also perform an intra-document join in which nested array elements are joined with their parent element within a document in a manner very similar to the way a join is performed between two tables in the relational world.
-
Your queries can return documents from the database as it is, or you can project any custom JSON shape you want based on as much or as little of the document data that you want.
-
SQL in DocumentDB supports many of the common operators including − Arithmetic and bitwise operations AND and OR logic Equality and range comparisons String concatenation
-
The query language also supports a host of built-in functions.
DocumentDB SQL - Select Clause
Azure 门户有一个查询浏览器，它让我们针对我们的 DocumentDB 数据库运行任何 SQL 查询。我们将使用查询浏览器演示查询语言的许多不同功能和特性，从最简单的查询开始。
The Azure portal has a Query Explorer that lets us run any SQL query against our DocumentDB database. We will use the Query Explorer to demonstrate the many different capabilities and features of the query language starting with the simplest possible query.
Step 1 - 打开 Azure 门户并在数据库边栏中单击查询浏览器边栏。
Step 1 − Open the Azure Portal, and in the database blade, click the Query Explorer blade.
请记住，查询在集合范围内运行，所以查询浏览器允许我们在该下拉菜单中选择集合。我们将让它设置为我们的包含三个文档的 Families 集合。让我们在该示例中考虑三个文档。
Remember that queries run within the scope of a collection, and so the Query Explorer lets us choose the collection in this dropdown. We will leave it set to our Families collection that contains the three documents. Let’s consider these three documents in this example.
以下是 AndersenFamily 文档。
Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}查询浏览器打开并带有这样简单的查询 SELECT * FROM c，它简单地从集合检索所有文档。尽管简单，但它仍然与关系数据库中的等效查询有很大的不同。
The Query Explorer opens up with this simple query SELECT * FROM c, which simply retrieves all documents from the collection. Although it is simple, it’s still quite different than the equivalent query in a relational database.
Step 2 - 在关系数据库中，SELECT * 表示返回所有列，而在 DocumentDB 中，这意味着您希望结果中的每个文档完全按其在数据库中存储的方式返回。
Step 2 − In relational databases, SELECT * means return all columns while in DocumentDB. It means that you want each document in your result to be returned exactly as it’s stored in the database.
但是，当您选择特定属性和表达式而不是仅仅发出 SELECT * 时，那么您就会针对结果中的每个文档规划您想要的新形状。
But when you select specific properties and expressions instead of simply issuing a SELECT *, then you are projecting a new shape that you want for each document in the result.
Step 3 - 单击“运行”执行查询并打开结果边栏。
Step 3 − Click ‘Run’ to execute query and open the Results blade.
可以看到检索到 WakefieldFamily、SmithFamily 和 AndersonFamily。
As can be seen the WakefieldFamily, the SmithFamily, and the AndersonFamily are retrieved.
以下三份文档是 SELECT * FROM c 查询结果检索到的。
Following are the three documents which are retrieved as a result of the SELECT * FROM c query.
[
{
"id": "WakefieldFamily",
"parents": [
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Miller",
"givenName": "Ben"
}
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{
"givenName": "Charlie Brown",
"type": "Dog"
},
{
"givenName": "Tiger",
"type": "Cat"
},
{
"givenName": "Princess",
"type": "Cat"
}
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{
"givenName": "Jake",
"type": "Snake"
}
]
}
],
"location": {
"state": "NY",
"county": "Manhattan",
"city": "NY"
},
"isRegistered": false,
"_rid": "Ic8LAJFujgECAAAAAAAAAA==",
"_ts": 1450541623,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/",
"_etag": "\"00000500-0000-0000-0000-567582370000\"",
"_attachments": "attachments/"
},
{
"id": "SmithFamily",
"parents": [
{
"familyName": "Smith",
"givenName": "James"
},
{
"familyName": "Curtis",
"givenName": "Helen"
}
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{
"givenName": "Tweetie",
"type": "Bird"
}
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true,
"_rid": "Ic8LAJFujgEDAAAAAAAAAA==",
"_ts": 1450541623,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEDAAAAAAAAAA==/",
"_etag": "\"00000600-0000-0000-0000-567582370000\"",
"_attachments": "attachments/"
},
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{
"firstName": "Thomas",
"relationship": "father"
},
{
"firstName": "Mary Kay",
"relationship": "mother"
}
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [
"givenName": "Fluffy",
"type": "Rabbit"
]
}
],
"location": {
"state": "WA",
"county": "King",
"city": "Seattle"
},
"isRegistered": true,
"_rid": "Ic8LAJFujgEEAAAAAAAAAA==",
"_ts": 1450541624,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/",
"_etag": "\"00000700-0000-0000-0000-567582380000\"",
"_attachments": "attachments/"
}
]但是，这些结果还包括系统生成的所有以下划线字符为前缀的属性。
However, these results also include the system-generated properties that are all prefixed with the underscore character.
DocumentDB SQL - From Clause
在本章中，我们将介绍 FROM 子句，其与常规 SQL 中的标准 FROM 子句的作用完全不同。
In this chapter, we will cover the FROM clause, which works nothing like a standard FROM clause in regular SQL.
查询总是运行在特定集合的上下文中，不能在集合中的文档之间进行联接，这让我们疑惑为什么需要 FROM 子句。事实上，我们不需要，但是如果不包含它，那么我们就不会查询集合中的文档。
Queries always run within the context of a specific collection and cannot join across documents within the collection, which makes us wonder why we need a FROM clause. In fact, we don’t, but if we don’t include it, then we won’t be querying documents in the collection.
此子句的目的是指定查询必须在其上运行的数据源。通常整个集合都是源，但也可以指定集合的子集。 FROM <from_specification> 子句是可选的，除非在查询中对源进行了筛选或投影。
The purpose of this clause is to specify the data source upon which the query must operate. Commonly the whole collection is the source, but one can specify a subset of the collection instead. The FROM <from_specification> clause is optional unless the source is filtered or projected later in the query.
让我们再次看同一个例子。以下是 AndersenFamily 文档。
Let’s take a look at the same example again. Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}
在上面的查询中，“ SELECT * FROM c ”表示整个 Families 集合是用来枚举的源。
In the above query, “SELECT * FROM c” indicates that the entire Families collection is the source over which to enumerate.
Sub-documents
也可以将源缩小到更小的子集。当我们只想检索每个文档中的子树时，子根就可以成为源，如下面的示例所示。
The source can also be reduced to a smaller subset. When we want to retrieve only a subtree in each document, the sub-root could then become the source, as shown in the following example.
当我们运行以下查询时 −
When we run the following query −
SELECT * FROM Families.parents将检索以下子文档。
The following sub-documents will be retrieved.
[
[
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Miller",
"givenName": "Ben"
}
],
[
{
"familyName": "Smith",
"givenName": "James"
},
{
"familyName": "Curtis",
"givenName": "Helen"
}
],
[
{
"firstName": "Thomas",
"relationship": "father"
},
{
"firstName": "Mary Kay",
"relationship": "mother"
}
]
]根据此查询结果，我们可以看到仅检索了 parents 子文档。
As a result of this query, we can see that only the parents sub-documents are retrieved.
DocumentDB SQL - Where Clause
在本章中，我们将介绍 WHERE 子句，它也像 FROM 子句一样是可选的。它用于在从源提供的 JSON 文档形式获取数据时指定条件。任何 JSON 文档都必须评估指定条件为“true”，才能被视为结果。如果给定的条件满足，则仅以 JSON 文档形式返回特定数据。我们可以使用 WHERE 子句过滤记录并仅获取必要的记录。
In this chapter, we will cover the WHERE clause, which is also optional like FROM clause. It is used to specify a condition while fetching the data in the form of JSON documents provided by the source. Any JSON document must evaluate the specified conditions to be "true" to be considered for the result. If the given condition is satisfied, only then it returns specific data in the form of JSON document(s). We can use WHERE clause to filter the records and fetch only necessary records.
在该示例中，我们将考虑同一三个文档。以下是 AndersenFamily 文档。
We will consider the same three documents in this example. Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}让我们看一个简单的示例，其中使用 WHERE 子句。
Let’s take a look at a simple example in which WHERE clause is used.
此查询中，在 WHERE 子句中指定了 (WHERE f.id = "WakefieldFamily") 条件。
In this query, in WHERE clause, the (WHERE f.id = "WakefieldFamily") condition is specified.
SELECT *
FROM f
WHERE f.id = "WakefieldFamily"执行上述查询时，它会返回 WakefieldFamily 的完整 JSON 文档，如下面的输出所示。
When the above query is executed, it will return the complete JSON document for WakefieldFamily as shown in the following output.
[
{
"id": "WakefieldFamily",
"parents": [
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Miller",
"givenName": "Ben"
}
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{
"givenName": "Charlie Brown",
"type": "Dog"
},
{
"givenName": "Tiger",
"type": "Cat"
},
{
"givenName": "Princess",
"type": "Cat"
}
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{
"givenName": "Jake",
"type": "Snake"
}
]
}
],
"location": {
"state": "NY",
"county": "Manhattan",
"city": "NY"
},
"isRegistered": false,
"_rid": "Ic8LAJFujgECAAAAAAAAAA==",
"_ts": 1450541623,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/",
"_etag": "\"00000500-0000-0000-0000-567582370000\"",
"_attachments": "attachments/"
}
]DocumentDB SQL - Operators
运算符是主要在 SQL WHERE 子句中用来执行运算（如比较和算术运算）的保留字或字符。DocumentDB SQL 还支持各种标量表达式。最常用的表达式是 binary and unary expressions 。
An operator is a reserved word or a character used primarily in an SQL WHERE clause to perform operation(s), such as comparisons and arithmetic operations. DocumentDB SQL also supports a variety of scalar expressions. The most commonly used are binary and unary expressions.
目前支持以下 SQL 运算符，并且可以在查询中使用它们。
The following SQL operators are currently supported and can be used in queries.
SQL Comparison Operators
以下是 DocumentDB SQL 语法中所有比较运算符的列表。
Following is a list of all the comparison operators available in DocumentDB SQL grammar.
S.No. |
Operators & Description |
1 |
= Checks if the values of two operands are equal or not. If yes, then condition becomes true. |
2 |
!= Checks if the values of two operands are equal or not. If values are not equal then condition becomes true. |
3 |
<> Checks if the values of two operands are equal or not. If values are not equal then condition becomes true. |
4 |
> Checks if the value of left operand is greater than the value of right operand. If yes, then condition becomes true. |
5 |
< Checks if the value of left operand is less than the value of right operand. If yes, then condition becomes true. |
6 |
>= Checks if the value of left operand is greater than or equal to the value of right operand. If yes, then condition becomes true. |
7 |
⇐ Checks if the value of left operand is less than or equal to the value of right operand. If yes, then condition becomes true. |
SQL Logical Operators
以下是 DocumentDB SQL 语法中所有逻辑运算符的列表。
Following is a list of all the logical operators available in DocumentDB SQL grammar.
S.No. |
Operators & Description |
1 |
AND The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause. |
2 |
BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. |
3 |
IN The IN operator is used to compare a value to a list of literal values that have been specified. |
4 |
OR The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause. |
5 |
NOT The NOT operator reverses the meaning of the logical operator with which it is used. For example, NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. |
SQL Arithmetic Operators
以下是 DocumentDB SQL 语法中所有算术运算符的列表。
Following is a list of all the arithmetic operators available in DocumentDB SQL grammar.
S.No. |
Operators & Description |
1 |
+ Addition − Adds values on either side of the operator. |
2 |
- Subtraction − Subtracts the right hand operand from the left hand operand. |
3 |
* Multiplication − Multiplies values on either side of the operator. |
4 |
/ Division − Divides the left hand operand by the right hand operand. |
5 |
% Modulus − Divides the left hand operand by the right hand operand and returns the remainder. |
在这个示例中我们也会考虑相同的文档。以下是 AndersenFamily 文档。
We will consider the same documents in this example as well. Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来通过一个简单的示例了解一下 WHERE 子句中比较运算符的使用。
Let’s take a look at a simple example in which a comparison operator is used in WHERE clause.
在这个查询中，在 WHERE 子句中指定了 (WHERE f.id = "WakefieldFamily") 条件，它将检索 id 等于 WakefieldFamily 的文档。
In this query, in WHERE clause, the (WHERE f.id = "WakefieldFamily") condition is specified, and it will retrieve the document whose id is equal to WakefieldFamily.
SELECT *
FROM f
WHERE f.id = "WakefieldFamily"执行上述查询时，它会返回 WakefieldFamily 的完整 JSON 文档，如下面的输出所示。
When the above query is executed, it will return the complete JSON document for WakefieldFamily as shown in the following output.
[
{
"id": "WakefieldFamily",
"parents": [
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Miller",
"givenName": "Ben"
}
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{
"givenName": "Charlie Brown",
"type": "Dog"
},
{
"givenName": "Tiger",
"type": "Cat"
},
{
"givenName": "Princess",
"type": "Cat"
}
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{
"givenName": "Jake",
"type": "Snake"
}
]
}
],
"location": {
"state": "NY",
"county": "Manhattan",
"city": "NY"
},
"isRegistered": false,
"_rid": "Ic8LAJFujgECAAAAAAAAAA==",
"_ts": 1450541623,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/",
"_etag": "\"00000500-0000-0000-0000-567582370000\"",
"_attachments": "attachments/"
}
]我们来看另一个示例，其中该查询将检索成绩大于 5 的儿童数据。
Let’s take a look at another example in which the query will retrieve the children data whose grade is greater than 5.
SELECT *
FROM Families.children[0] c
WHERE (c.grade > 5)执行上述查询后，将按照输出中所示检索以下子文档。
When the above query is executed, it will retrieve the following sub document as shown in the output.
[
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{
"givenName": "Charlie Brown",
"type": "Dog"
},
{
"givenName": "Tiger",
"type": "Cat"
},
{
"givenName": "Princess",
"type": "Cat"
}
]
}
]DocumentDB SQL - Between Keyword
BETWEEN 关键字用于针对 SQL 中的如同值范围的查询进行表达。BETWEEN 可用于字符串或数字。在 DocumentDB 和 ANSI SQL 中使用 BETWEEN 的主要区别在于，您可以在混合类型的属性上表达范围查询。
The BETWEEN keyword is used to express queries against ranges of values like in SQL. BETWEEN can be used against strings or numbers. The main difference between using BETWEEN in DocumentDB and ANSI SQL is that you can express range queries against properties of mixed types.
例如，在某些文档中，您可能将“grade”作为数字，而在其他文档中它可能是字符串。在这些情况下，“未定义”两种不同类型的结果之间的比较，并且该文档将被跳过。
For example, in some document it is possible that you might have "grade" as a number and in other documents it might be strings. In these cases, a comparison between two different types of results is "undefined", and the document will be skipped.
我们来考虑先前示例中的三个文档。以下是 AndersenFamily 文档。
Let us consider the three documents from the previous example. Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来通过一个示例了解一下，其中该查询返回所有第一个孩子的成绩介于 1-5（包括边界）的家庭文档。
Let’s take a look at an example, where the query returns all family documents in which the first child’s grade is between 1-5 (both inclusive).
以下是使用 BETWEEN 关键字并随后使用 AND 逻辑运算符的查询。
Following is the query in which BETWEEN keyword is used and then AND logical operator.
SELECT *
FROM Families.children[0] c
WHERE c.grade BETWEEN 1 AND 5执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [
{
"givenName": "Fluffy",
"type": "Rabbit"
}
]
}
]要显示超出先前示例范围的成绩，请如以下查询所示使用 NOT BETWEEN。
To display the grades outside the range of the previous example, use NOT BETWEEN as shown in the following query.
SELECT *
FROM Families.children[0] c
WHERE c.grade NOT BETWEEN 1 AND 5执行此查询时。它生成以下输出。
When this query is executed. It produces the following output.
[
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{
"givenName": "Charlie Brown",
"type": "Dog"
},
{
"givenName": "Tiger",
"type": "Cat"
},
{
"givenName": "Princess",
"type": "Cat"
}
]
}
]DocumentDB SQL - In Keyword
IN 关键字可用于检查指定值是否与列表中的任何值匹配。IN 运算符允许您在 WHERE 子句中指定多个值。IN 等同于链接多个 OR 子句。
The IN keyword can be used to check whether a specified value matches any value in a list. The IN operator allows you to specify multiple values in a WHERE clause. IN is equivalent to chaining multiple OR clauses.
与先前示例中所做一样，考虑相同的三个文档。以下是 AndersenFamily 文档。
The similar three documents are considered as done in earlier examples. Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来看一个简单的例子。
Let’s take a look at a simple example.
以下查询将检索 familyName 为 “Smith”或 Wakefield 的数据。
Following is the query which will retrieve the data whose familyName is either “Smith” or Wakefield.
SELECT *
FROM Families.parents[0] f
WHERE f.familyName IN ('Smith', 'Wakefield')执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Smith",
"givenName": "James"
}
]我们来考虑另一个示例，其中将检索所有 id 为 "SmithFamily" 或 "AndersenFamily" 的家庭文档。以下是查询。
Let’s consider another simple example in which all family documents will be retrieved where the id is one of "SmithFamily" or "AndersenFamily". Following is the query.
SELECT *
FROM Families
WHERE Families.id IN ('SmithFamily', 'AndersenFamily')执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"id": "SmithFamily",
"parents": [
{
"familyName": "Smith",
"givenName": "James"
},
{
"familyName": "Curtis",
"givenName": "Helen"
}
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{
"givenName": "Tweetie",
"type": "Bird"
}
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true,
"_rid": "Ic8LAJFujgEDAAAAAAAAAA==",
"_ts": 1450541623,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEDAAAAAAAAAA==/",
"_etag": "\"00000600-0000-0000-0000-567582370000\"",
"_attachments": "attachments/"
},
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{
"firstName": "Thomas",
"relationship": "father"
},
{
"firstName": "Mary Kay",
"relationship": "mother"
}
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [
{
"givenName": "Fluffy",
"type": "Rabbit"
}
]
}
],
"location": {
"state": "WA",
"county": "King",
"city": "Seattle"
},
"isRegistered": true,
"_rid": "Ic8LAJFujgEEAAAAAAAAAA==",
"_ts": 1450541624,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/",
"_etag": "\"00000700-0000-0000-0000-567582380000\"",
"_attachments": "attachments/"
}
]DocumentDB SQL - Value Keyword
当您知道您仅返回单个值时，VALUE 关键字可以通过避免创建完整对象开销来帮助生成精简的结果集。VALUE 关键字提供了一种返回 JSON 值的方法。
When you know you’re only returning a single value, then the VALUE keyword can help produce a leaner result set by avoiding the overhead of creating a full-blown object. The VALUE keyword provides a way to return JSON value.
我们来看一个简单的例子。
Let’s take a look at a simple example.
以下是包含 VALUE 关键字的查询。
Following is the query with VALUE keyword.
SELECT VALUE "Hello World, this is DocumentDB SQL Tutorial"当执行此查询时，他返回标量“Hello World，这是 DocumentDB SQL 教程”。
When this query is executed, it returns the scalar "Hello World, this is DocumentDB SQL Tutorial".
[
"Hello World, this is DocumentDB SQL Tutorial"
]在另一个例子中，我们来看看上面例子中的三个文档。
In another example, let’s consider the three documents from the previous examples.
以下是 AndersenFamily 文档。
Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}以下是查询。
Following is the query.
SELECT VALUE f.location
FROM Families f当执行此查询时，他返回地址而不带位置标签。
When this query is executed, it return the returns the address without the location label.
[
{
"state": "NY",
"county": "Manhattan",
"city": "NY"
},
{
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
{
"state": "WA",
"county": "King",
"city": "Seattle"
}
]如果我们现在指定相同的查询而无 VALUE 关键字，那么将会返回带有位置标签的地址。以下是查询。
If we now specify the same query without VALUE Keyword, then it will return the address with location label. Following is the query.
SELECT f.location
FROM Families f执行此查询时，将得到以下输出。
When this query is executed, it produces the following output.
[
{
"location": {
"state": "NY",
"county": "Manhattan",
"city": "NY"
}
},
{
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
}
},
{
"location": {
"state": "WA",
"county": "King",
"city": "Seattle"
}
}
]DocumentDB SQL - Order By Clause
Microsoft Azure DocumentDB 支持使用 JSON 文档中的 SQL 查询文档。您可以使用查询中的 ORDER BY 子句按照数字和字符串对集合中的文档进行排序。该子句可以包含一个可选的 ASC/DESC 参数，以指定必须检索结果的顺序。
Microsoft Azure DocumentDB supports querying documents using SQL over JSON documents. You can sort documents in the collection on numbers and strings using an ORDER BY clause in your query. The clause can include an optional ASC/DESC argument to specify the order in which results must be retrieved.
我们将考虑与前例中相同的文档。
We will consider the same documents as in the previous examples.
以下是 AndersenFamily 文档。
Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来看一个简单的例子。
Let’s take a look at a simple example.
以下是包含 ORDER BY 关键字的查询。
Following is the query which contains the ORDER BY keyword.
SELECT f.id, f.children[0].givenName,f.children[0].grade
FROM Families f
ORDER BY f.children[0].grade执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"id": "SmithFamily",
"givenName": "Michelle",
"grade": 1
},
{
"id": "AndersenFamily",
"grade": 5
},
{
"id": "WakefieldFamily",
"givenName": "Jesse",
"grade": 6
}
]我们再考虑另一个简单的例子。
Let’s consider another simple example.
以下是对包含可选 ORDER BY 关键字和 DESC 关键字的查询，在其后加上注释。
Following is the query which contains the ORDER BY keyword and DESC optional keyword.
SELECT f.id, f.parents[0].familyName
FROM Families f
ORDER BY f.parents[0].familyName DESC执行上述查询时，将会产生以下输出。
When the above query is executed, it will produce the following output.
[
{
"id": "WakefieldFamily",
"familyName": "Wakefield"
},
{
"id": "SmithFamily",
"familyName": "Smith"
},
{
"id": "AndersenFamily"
}
]DocumentDB SQL - Iteration
在 DocumentDB SQL 中，Microsoft 添加了一种新构造，可与 IN 关键字一起使用，为遍历 JSON 数组提供支持。FROM 子句中提供了对迭代的支持。
In DocumentDB SQL, Microsoft has added a new construct which can be used with IN keyword to provide support for iterating over JSON arrays. The support for iteration is provided in the FROM clause.
我们将再次考虑前面例子里类似的三个文档。
We will consider similar three documents from the previous examples again.
以下是 AndersenFamily 文档。
Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来看一个没有 FROM 子句中的 IN 关键字的简单示例。
Let’s take a look at a simple example without IN keyword in FROM clause.
以下是将返回 Families 集合中所有父母的查询。
Following is the query which will return all the parents from the Families collection.
SELECT *
FROM Families.parents执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
[
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Miller",
"givenName": "Ben"
}
],
[
{
"familyName": "Smith",
"givenName": "James"
},
{
"familyName": "Curtis",
"givenName": "Helen"
}
],
[
{
"firstName": "Thomas",
"relationship": "father"
},
{
"firstName": "Mary Kay",
"relationship": "mother"
}
]
]如上面输出所示，每个家庭的父母以单独的 JSON 数组显示。
As can be seen in the above output, the parents of each family is displayed in a separate JSON array.
我们来看相同的例子，但这一次，我们将在 FROM 子句中使用 IN 关键字。
Let’s take a look at the same example, however this time we will use the IN keyword in FROM clause.
以下是包含 IN 关键字的查询。
Following is the query which contains the IN keyword.
SELECT *
FROM c IN Families.parents执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Miller",
"givenName": "Ben"
},
{
"familyName": "Smith",
"givenName": "James"
},
{
"familyName": "Curtis",
"givenName": "Helen"
},
{
"firstName": "Thomas",
"relationship": "father"
},
{
"firstName": "Mary Kay",
"relationship": "mother"
}
{
"id": "WakefieldFamily",
"givenName": "Jesse",
"grade": 6
}
]在上面例子中，可以看出，通过迭代，在集合中的父母上执行迭代的查询有一个不同的输出数组。因此，每个家庭的所有父母都添加到单个数组中。
In the above example, it can be seen that with iteration, the query that performs iteration over parents in the collection has different output array. Hence, all the parents from each family are added into a single array.
DocumentDB SQL - Joins
在关系型数据库中，Join 子句用于合并数据库中来自两个或更多表的记录，并且在设计归一化架构时，非常需要连接跨表。由于 DocumentDB 处理无架构文档的非规范化数据模型，因此 DocumentDB SQL 中的 JOIN 是“selfjoin”的逻辑等效。
In relational databases, the Joins clause is used to combine records from two or more tables in a database, and the need to join across tables is very important while designing normalized schemas. Since DocumentDB deals with the denormalized data model of schema-free documents, the JOIN in DocumentDB SQL is the logical equivalent of a "selfjoin".
让我们考虑一下前一个示例中的三个文档。
Let’s consider the three documents as in the previous examples.
以下是 AndersenFamily 文档。
Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来看一个示例，以了解 JOIN 子句的工作原理。
Let’s take a look at an example to understand how the JOIN clause works.
以下是将根连接到子文档的查询。
Following is the query which will join the root to children subdocument.
SELECT f.id
FROM Families f
JOIN c IN f.children执行上述查询时，将会产生以下输出。
When the above query is executed, it will produce the following output.
[
{
"id": "WakefieldFamily"
},
{
"id": "WakefieldFamily"
},
{
"id": "SmithFamily"
},
{
"id": "SmithFamily"
},
{
"id": "AndersenFamily"
}
]在上述示例中，连接在文档根和子根之间，在两个 JSON 对象之间生成笛卡尔积。以下是需要注意的几个要点 -
In the above example, the join is between the document root and the children sub-root which makes a cross-product between two JSON objects. Following are certain points to note −
-
In the FROM clause, the JOIN clause is an iterator.
-
The first two documents WakefieldFamily and SmithFamily contain two children, hence the result set also contains the cross-product which produces a separate object for each child.
-
The third document AndersenFamily contains only one children, hence there is only a single object corresponding to this document.
让我们来看相同的示例，但这一次，我们还检索子名称，以便更好地理解 JOIN 子句。
Let’s take a look at the same example, however this time we retrieve the child name as well for better understanding of JOIN clause.
以下是将根连接到子文档的查询。
Following is the query which will join the root to children subdocument.
SELECT
f.id AS familyName,
c.givenName AS childGivenName,
c.firstName AS childFirstName
FROM Families f
JOIN c IN f.children执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"familyName": "WakefieldFamily",
"childGivenName": "Jesse"
},
{
"familyName": "WakefieldFamily",
"childGivenName": "Lisa"
},
{
"familyName": "SmithFamily",
"childGivenName": "Michelle"
},
{
"familyName": "SmithFamily",
"childGivenName": "John"
},
{
"familyName": "AndersenFamily",
"childFirstName": "Henriette Thaulow"
}
]DocumentDB SQL - Aliasing
在关系数据库中，SQL 别名用于临时重命名表或列标题。类似地，在 DocumentDB 中，别名用于临时重命名 JSON 文档、子文档、对象或任何字段。
In relational databases, SQL aliases are used to temporarily rename a table or a column heading. Similarly, in DocumentDB, aliases are used to temporarily rename a JSON document, sub-document, object or any field.
重命名是一个临时更改，实际文档并不会更改。基本上，别名是为了使字段/文档名称更具可读性。对于别名，使用可选的 AS 关键字。
The renaming is a temporary change and the actual document does not change. Basically, aliases are created to make field/document names more readable. For aliasing, AS keyword is used which is optional.
我们从前一个例子中考虑三个类似的文档。
Let’s consider three similar documents from the ones used in previous examples.
以下是 AndersenFamily 文档。
Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来看看一个讨论别名的例子。
Let’s take a look at an example to discuss the aliases.
以下是将根与子文档连接的查询。我们有别名，例如 f.id AS familyName、c.givenName AS childGivenName 和 c.firstName AS childFirstName。
Following is the query which will join the root to children subdocument. We have aliases such as f.id AS familyName, c.givenName AS childGivenName, and c.firstName AS childFirstName.
SELECT
f.id AS familyName,
c.givenName AS childGivenName,
c.firstName AS childFirstName
FROM Families f
JOIN c IN f.children执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"familyName": "WakefieldFamily",
"childGivenName": "Jesse"
},
{
"familyName": "WakefieldFamily",
"childGivenName": "Lisa"
},
{
"familyName": "SmithFamily",
"childGivenName": "Michelle"
},
{
"familyName": "SmithFamily",
"childGivenName": "John"
},
{
"familyName": "AndersenFamily",
"childFirstName": "Henriette Thaulow"
}
]上面的输出显示文件名称已更改，但这是临时更改，原始文档未修改。
The above output shows that the filed names are changed, but it is a temporary change and the original documents are not modified.
DocumentDB SQL - Array Creation
在 DocumentDB SQL 中，Microsoft 添加了一个关键功能，借助此功能我们可以轻松创建数组。这意味着当我们运行查询时，它将创建类似于 JSON 对象的集合数组作为查询的结果。
In DocumentDB SQL, Microsoft has added a key feature with the help of which we can easily create an array. It means when we run a query, then as a result it will create an array of collection similar to JSON object as a result of query.
让我们考虑与前例中相同的文档。
Let’s consider the same documents as in the previous examples.
以下是 AndersenFamily 文档。
Following is the AndersenFamily document.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}以下是 SmithFamily 文档。
Following is the SmithFamily document.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}以下是 WakefieldFamily 文档。
Following is the WakefieldFamily document.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来看一个例子。
Let’s take a look at an example.
以下是将返回每个家庭的姓氏和地址的查询。
Following is the query which will return the family name and address of each family.
SELECT f.id AS FamilyName,
[f.location.city, f.location.county, f.location.state] AS Address
FROM Families f可以看到 city、county 和 state 字段用方括号括起来，这将创建一个数组，此数组名为 Address。当执行上述查询时，它会生成以下输出。
As can be seen city, county and state fields are enclosed in square brackets, which will create an array and this array is named Address. When the above query is executed, it produces the following output.
[
{
"FamilyName": "WakefieldFamily",
"Address": [
"NY",
"Manhattan",
"NY"
]
},
{
"FamilyName": "SmithFamily",
"Address": [
"Forest Hills",
"Queens",
"NY"
]
},
{
"FamilyName": "AndersenFamily",
"Address": [
"Seattle",
"King",
"WA"
]
}
]在上述输出中，城市、县和州信息被添加到 Address 数组中。
The city, county, and state information are added in the Address array in the above output.
DocumentDB SQL - Scalar Expressions
在 DocumentDB SQL 中，SELECT 子句还支持标量表达式，如常量、算术表达式、逻辑表达式等。通常，标量查询很少使用，因为它们不会实际查询集合中的文档，只是评估表达式。但使用标量表达式查询来学习基础知识、如何在查询中使用表达式和塑造 JSON，仍然很有帮助，并且这些概念直接适用于在集合中的文档中所运行的实际查询。
In DocumentDB SQL, the SELECT clause also supports scalar expressions like constants, arithmetic expressions, logical expressions, etc. Normally, scalar queries are rarely used, because they don’t actually query documents in the collection, they just evaluate expressions. But it’s still helpful to use scalar expression queries to learn the basics, how to use expressions and shape JSON in a query, and these concepts apply directly to the actual queries you’ll be running against documents in a collection.
让我们来看一个包含多个标量查询的示例。
Let’s take a look at an example which contains multiple scalar queries.
在查询浏览器中，只选择要执行的文本并单击“运行”。我们先运行第一个。
In the Query Explorer, select just the text to be executed and click ‘Run’. Let’s run this first one.
SELECT "Hello"执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"$1": "Hello"
}
]此输出可能看起来有些令人困惑，因此我们来将其分解一下。
This output may look a bit confusing, so let’s break it down.
-
First, as we saw in the last demo, query results are always contained in square brackets because they are returned as a JSON array, even results from scalar expression queries like this one that only returns a single document.
-
We have an array with one document in it, and that document has a single property in it for the single expression in the SELECT statement.
-
The SELECT statement doesn’t provide a name for this property, thus DocumentDB auto generates one using $1.
-
This is usually not what we want, which is why we can use AS to alias the expression in the query, which sets the property name in the generated document the way you’d like it to be, word, in this example.
SELECT "Hello" AS word执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"word": "Hello"
}
]同样，以下是一个简单的查询。
Similarly, following is another simple query.
SELECT ((2 + 11 % 7)-2)/3查询检索以下输出。
The query retrieves the following output.
[
{
"$1": 1.3333333333333333
}
]让我们再看另一个嵌套数组和嵌入对象的塑造示例。
Let’s take a look at another example of shaping nested arrays and embedded objects.
SELECT
{
"words1":
["Hello", "World"],
"words2":
["How", "Are", "You?"]
} AS allWords执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"allWords": {
"words1": [
"Hello",
"World"
],
"words2": [
"How",
"Are",
"You?"
]
}
}
]DocumentDB SQL - Parameterized
在关系数据库中，参数化查询是一个在其中为参数使用占位符，并在执行时提供参数值的查询。DocumentDB 也支持参数化查询，并且参数化查询中的参数可以使用熟悉的 @ 符号表示。使用参数化查询的最重要原因是避免 SQL 注入攻击。它还可以提供对用户输入的可靠处理和转义。
In relational databases, a parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. DocumentDB also supports parameterized queries, and parameters in parameterized query can be expressed with the familiar @ notation. The most important reason to use parameterized queries is to avoid SQL injection attacks. It can also provide robust handling and escaping of user input.
让我们来看一个将使用 .Net SDK 的示例。以下是将删除集合的代码。
Let’s take a look at an example where we will be using the .Net SDK. Following is the code which will delete the collection.
private async static Task DeleteCollection(DocumentClient client, string collectionId) {
Console.WriteLine();
Console.WriteLine(">>> Delete Collection {0} in {1} <<<",
collectionId, _database.Id);
var query = new SqlQuerySpec {
QueryText = "SELECT * FROM c WHERE c.id = @id",
Parameters = new SqlParameterCollection { new SqlParameter { Name =
"@id", Value = collectionId } }
};
DocumentCollection collection = client.CreateDocumentCollectionQuery(database.SelfLink,
query).AsEnumerable().First();
await client.DeleteDocumentCollectionAsync(collection.SelfLink);
Console.WriteLine("Deleted collection {0} from database {1}",
collectionId, _database.Id);
}参数化查询的构造如下。
The construction of a parameterized query is as follows.
var query = new SqlQuerySpec {
QueryText = "SELECT * FROM c WHERE c.id = @id",
Parameters = new SqlParameterCollection { new SqlParameter { Name =
"@id", Value = collectionId } }
};我们不会对 collectionId 进行硬编码，因此此方法可用于删除任何集合。我们可以使用“@”符号为参数名称添加前缀，类似于 SQL Server。
We are not hardcoding the collectionId so this method can be used to delete any collection. We can use ‘@’ symbol to prefix parameter names, similar to SQL Server.
在上述示例中，我们通过 Id 查询一个特定集合，其中 Id 参数在此 SqlParameterCollection 中定义，该 SqlParameterCollection 已分配给此 SqlQuerySpec 的参数属性。然后，SDK 负责为 DocumentDB 构造最终查询字符串，其中嵌入了 collectionId。我们运行查询，然后使用其 SelfLink 删除集合。
In the above example, we are querying for a specific collection by Id where the Id parameter is defined in this SqlParameterCollection assigned to the parameter’s property of this SqlQuerySpec. The SDK then does the work of constructing the final query string for DocumentDB with the collectionId embedded inside of it. We run the query and then use its SelfLink to delete the collection.
以下是 CreateDocumentClient 任务实现。
Following is the CreateDocumentClient task implementation.
private static async Task CreateDocumentClient() {
// Create a new instance of the DocumentClient
using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
database = client.CreateDatabaseQuery("SELECT * FROM
c WHERE c.id = 'earthquake'").AsEnumerable().First();
collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
"SELECT * FROM c WHERE c.id = 'myfirstdb'").AsEnumerable().First();
await DeleteCollection(client, "MyCollection1");
await DeleteCollection(client, "MyCollection2");
}
}当代码执行时，它会产生以下输出。
When the code is executed, it produces the following output.
**** Delete Collection MyCollection1 in mydb ****
Deleted collection MyCollection1 from database myfirstdb
**** Delete Collection MyCollection2 in mydb ****
Deleted collection MyCollection2 from database myfirstdb让我们来看另一个示例。我们可以编写一个将姓氏和地址州作为参数的查询，然后根据用户输入为 lastname 和 location.state 的各种值执行该查询。
Let’s take a look at another example. We can write a query that takes last name and address state as parameters, and then executes it for various values of lastname and location.state based on the user input.
SELECT *
FROM Families f
WHERE f.lastName = @lastName AND f.location.state = @addressState然后可以将此请求作为一个参数化 JSON 查询发送给 DocumentDB，如下面的代码所示。
This request can then be sent to DocumentDB as a parameterized JSON query as shown in the following code.
{
"query": "SELECT * FROM Families f WHERE f.lastName = @lastName AND
f.location.state = @addressState",
"parameters": [
{"name": "@lastName", "value": "Wakefield"},
{"name": "@addressState", "value": "NY"},
]
}DocumentDB SQL - Built-in Function
DocumentDB 支持各种内置函数，用于可以在查询内使用的常见操作。有一系列函数可用于执行数学计算，还有在使用各种模式时非常有用的类型检查函数。这些函数可以测试某个属性是否存在，以及如果存在，它是否为数字、字符串、布尔值或对象。
DocumentDB supports a host of built-in functions for common operations that can be used inside queries. There are a bunch of functions for performing mathematical calculations, and also type checking functions that are extremely useful while working with varying schemas. These functions can test if a certain property exists and if it does whether it’s a number or a string, Boolean or object.
我们还获得了用于解析和操作字符串的这些便捷函数，以及用于处理数组的几个函数，这些函数允许您执行诸如连接数组和测试数组是否包含特定元素之类的操作。
We also get these handy functions for parsing and manipulating strings, as well as several functions for working with arrays allowing you to do things like concatenate arrays and test to see if an array contains a particular element.
以下是内置函数不同类型 -
Following are the different types of built-in functions −
S.No. |
Built-in Functions & Description |
1 |
Mathematical FunctionsThe mathematical functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value. |
2 |
Type Checking FunctionsThe type checking functions allow you to check the type of an expression within SQL queries. |
3 |
String FunctionsThe string functions perform an operation on a string input value and return a string, numeric or Boolean value. |
4 |
Array FunctionsThe array functions perform an operation on an array input value and return in the form of numeric, Boolean or array value. |
5 |
Spatial FunctionsDocumentDB also supports the Open Geospatial Consortium (OGC) built-in functions for geospatial querying. |
DocumentDB SQL - Linq to SQL Translation
在 DocumentDB 中，我们实际上使用 SQL 查询文档。如果我们进行 .NET 开发，还可以使用一个 LINQ 提供程序，它可以从 LINQ 查询中生成适当的 SQL。
In DocumentDB, we actually use SQL to query documents. If we are doing .NET development, there is also a LINQ provider that can be used and which can generate appropriate SQL from a LINQ query.
Supported Data Types
在 DocumentDB 中，随 DocumentDB .NET SDK 包含的 LINQ 提供程序支持所有 JSON 原始类型，它们如下所示 −
In DocumentDB, all JSON primitive types are supported in the LINQ provider included with the DocumentDB .NET SDK which are as follows −
-
Numeric
-
Boolean
-
String
-
Null
Supported Expression
以下标量表达式由随 DocumentDB .NET SDK 包含的 LINQ 提供程序支持。
The following scalar expressions are supported in the LINQ provider included with the DocumentDB .NET SDK.
-
Constant Values − Includes constant values of the primitive data types.
-
Property/Array Index Expressions − Expressions refer to the property of an object or an array element.
-
Arithmetic Expressions − Includes common arithmetic expressions on numerical and Boolean values.
-
String Comparison Expression − Includes comparing a string value to some constant string value.
-
Object/Array Creation Expression − Returns an object of compound value type or anonymous type or an array of such objects. These values can be nested.
Supported LINQ Operators
以下是随 DocumentDB .NET SDK 包含的 LINQ 提供程序中支持的 LINQ 运算符列表。
Here is a list of supported LINQ operators in the LINQ provider included with the DocumentDB .NET SDK.
-
Select − Projections translate to the SQL SELECT including object construction.
-
Where − Filters translate to the SQL WHERE, and support translation between && , || and ! to the SQL operators.
-
SelectMany − Allows unwinding of arrays to the SQL JOIN clause. Can be used to chain/nest expressions to filter array elements.
-
OrderBy and OrderByDescending − Translates to ORDER BY ascending/descending.
-
CompareTo − Translates to range comparisons. Commonly used for strings since they’re not comparable in .NET.
-
Take − Translates to the SQL TOP for limiting results from a query.
-
Math Functions − Supports translation from .NET’s Abs, Acos, Asin, Atan, Ceiling, Cos, Exp, Floor, Log, Log10, Pow, Round, Sign, Sin, Sqrt, Tan, Truncate to the equivalent SQL built-in functions.
-
String Functions − Supports translation from .NET’s Concat, Contains, EndsWith, IndexOf, Count, ToLower, TrimStart, Replace, Reverse, TrimEnd, StartsWith, SubString, ToUpper to the equivalent SQL built-in functions.
-
Array Functions − Supports translation from .NET’s Concat, Contains, and Count to the equivalent SQL built-in functions.
-
Geospatial Extension Functions − Supports translation from stub methods Distance, Within, IsValid, and IsValidDetailed to the equivalent SQL built-in functions.
-
User-Defined Extension Function − Supports translation from the stub method UserDefinedFunctionProvider.Invoke to the corresponding user-defined function.
-
Miscellaneous − Supports translation of coalesce and conditional operators. Can translate Contains to String CONTAINS, ARRAY_CONTAINS or the SQL IN depending on context.
我们来看一个使用 .Net SDK 的示例。以下三个文档我们将在本示例中考虑。
Let’s take a look at an example where we will be using the .Net SDK. Following are the three documents which we will be consider for this example.
New Customer 1
{
"name": "New Customer 1",
"address": {
"addressType": "Main Office",
"addressLine1": "123 Main Street",
"location": {
"city": "Brooklyn",
"stateProvinceName": "New York"
},
"postalCode": "11229",
"countryRegionName": "United States"
},
}New Customer 2
{
"name": "New Customer 2",
"address": {
"addressType": "Main Office",
"addressLine1": "678 Main Street",
"location": {
"city": "London",
"stateProvinceName": " London "
},
"postalCode": "11229",
"countryRegionName": "United Kingdom"
},
}New Customer 3
{
"name": "New Customer 3",
"address": {
"addressType": "Main Office",
"addressLine1": "12 Main Street",
"location": {
"city": "Brooklyn",
"stateProvinceName": "New York"
},
"postalCode": "11229",
"countryRegionName": "United States"
},
}以下是在 q 中使用 LINQ 进行查询的代码，但除非针对它运行 .ToList，否则它不会执行。
Following is the code in which we query using LINQ. We’ve defined a LINQ query in q, but it won’t execute until we run .ToList on it.
private static void QueryDocumentsWithLinq(DocumentClient client) {
Console.WriteLine();
Console.WriteLine("**** Query Documents (LINQ) ****");
Console.WriteLine();
Console.WriteLine("Quering for US customers (LINQ)");
var q =
from d in client.CreateDocumentQuery<Customer>(collection.DocumentsLink)
where d.Address.CountryRegionName == "United States"
select new {
Id = d.Id,
Name = d.Name,
City = d.Address.Location.City
};
var documents = q.ToList();
Console.WriteLine("Found {0} US customers", documents.Count);
foreach (var document in documents) {
var d = document as dynamic;
Console.WriteLine(" Id: {0}; Name: {1}; City: {2}", d.Id, d.Name, d.City);
}
Console.WriteLine();
}SDK 会将我们的 LINQ 查询转换成适用于 DocumentDB 的 SQL 语法，根据我们的 LINQ 语法生成 SELECT 和 WHERE 子句。
The SDK will convert our LINQ query into SQL syntax for DocumentDB, generating a SELECT and WHERE clause based on our LINQ syntax.
让我们从 CreateDocumentClient 任务调用以上查询。
Let’s call the above queries from the CreateDocumentClient task.
private static async Task CreateDocumentClient() {
// Create a new instance of the DocumentClient
using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
database = client.CreateDatabaseQuery("SELECT * FROM c WHERE c.id =
'myfirstdb'").AsEnumerable().First();
collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
"SELECT * FROM c WHERE c.id = 'MyCollection'").AsEnumerable().First();
QueryDocumentsWithLinq(client);
}
}执行以上代码时，会产生以下输出。
When the above code is executed, it produces the following output.
**** Query Documents (LINQ) ****
Quering for US customers (LINQ)
Found 2 US customers
Id: 7e9ad4fa-c432-4d1a-b120-58fd7113609f; Name: New Customer 1; City: Brooklyn
Id: 34e9873a-94c8-4720-9146-d63fb7840fad; Name: New Customer 1; City: BrooklynDocumentDB SQL - JavaScript Integration
如今，JavaScript 无处不在，而不仅仅在浏览器中。DocumentDB 作为一种现代版 T-SQL 接受 JavaScript，支持原生地对 JavaScript 逻辑进行事务执行，就在数据库引擎内部。DocumentDB 提供了一种编程模型，用于根据存储过程和触发器直接对集合中的基于 JavaScript 的应用程序逻辑进行执行。
These days JavaScript is everywhere, and not just in browsers. DocumentDB embraces JavaScript as a sort of modern day T-SQL and supports the transactional execution of JavaScript logic natively, right inside the database engine. DocumentDB provides a programming model for executing JavaScript-based application logic directly on the collections in terms of stored procedures and triggers.
我们来看一个创建简单存储过程的示例。以下步骤 −
Let’s take a look at an example where we create a simple store procedure. Following are the steps −
Step 1 − 创建一个新的控制台应用程序。
Step 1 − Create a new console applications.
Step 2 − 从 NuGet 添加 .NET SDK。我们此处使用 .NET SDK，这意味着我们将编写一些 C# 代码来创建、执行，然后删除我们的存储过程，但存储过程本身是用 JavaScript 编写的。
Step 2 − Add in the .NET SDK from NuGet. We are using the .NET SDK here, which means that we’ll be writing some C# code to create, execute, and then delete our stored procedure, but the stored procedure itself gets written in JavaScript.
Step 3 − 右键单击解决方案资源管理器中的项目。
Step 3 − Right-click on the project in Solution explorer.
Step 4 − 添加用于存储过程的新 JavaScript 文件并将其称为 HelloWorldStoreProce.js
Step 4 − Add a new JavaScript file for the stored procedure and call it HelloWorldStoreProce.js
每个存储过程都只是一个 JavaScript 函数，因此我们将创建一个新函数，并自然地也将此函数称为 HelloWorldStoreProce 。无论是否为函数命名都没有关系。DocumentDB 只会通过我们在创建时提供的 Id 来引用这个存储过程。
Every stored procedure is just a JavaScript function so we’ll create a new function and naturally we’ll also name this function HelloWorldStoreProce. It doesn’t matter if we give the function a name at all. DocumentDB will only refer to this stored procedure by the Id that we provide when we create it.
function HelloWorldStoreProce() {
var context = getContext();
var response = context.getResponse();
response.setBody('Hello, and welcome to DocumentDB!');
}存储过程的所有操作就是从上下文中获取 response 对象并调用其 setBody 方法，向调用方返回一个字符串。在 C# 代码中，我们将创建存储过程，执行它，然后删除它。
All the stored procedure does is obtain the response object from the context and call its setBody method to return a string to the caller. In C# code, we will create the stored procedure, execute it, and then delete it.
存储过程的范围是每个集合，因此我们将需要集合的 SelfLink 来创建存储过程。
Stored procedures are scoped per collection, therefore we will need the SelfLink of the collection to create the stored procedure.
Step 5 − 首先查询 myfirstdb 数据库，然后查询 MyCollection 集合。
Step 5 − First query for the myfirstdb database and then for the MyCollection collection.
创建存储过程就像创建 DocumentDB 中的任何其他资源一样。
Creating a stored procedure is just like creating any other resource in DocumentDB.
private async static Task SimpleStoredProcDemo() {
var endpoint = "https://azuredocdbdemo.documents.azure.com:443/";
var masterKey =
"BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";
using (var client = new DocumentClient(new Uri(endpoint), masterKey)) {
// Get database
Database database = client
.CreateDatabaseQuery("SELECT * FROM c WHERE c.id = 'myfirstdb'")
.AsEnumerable()
.First();
// Get collection
DocumentCollection collection = client
.CreateDocumentCollectionQuery(database.CollectionsLink, "SELECT * FROM
c WHERE c.id = 'MyCollection'")
.AsEnumerable()
.First();
// Create stored procedure
var sprocBody = File.ReadAllText(@"..\..\HelloWorldStoreProce.js");
var sprocDefinition = new StoredProcedure {
Id = "HelloWorldStoreProce",
Body = sprocBody
};
StoredProcedure sproc = await client.
CreateStoredProcedureAsync(collection.SelfLink, sprocDefinition);
Console.WriteLine("Created stored procedure {0} ({1})",
sproc.Id, sproc.ResourceId);
// Execute stored procedure
var result = await client.ExecuteStoredProcedureAsync(sproc.SelfLink);
Console.WriteLine("Executed stored procedure; response = {0}", result.Response);
// Delete stored procedure
await client.DeleteStoredProcedureAsync(sproc.SelfLink);
Console.WriteLine("Deleted stored procedure {0} ({1})",
sproc.Id, sproc.ResourceId);
}
}Step 6 - 首先使用新资源的 Id 创建一个定义对象，然后在 DocumentClient 对象上调用 Create 方法之一。对于一个存储过程来说，定义包括用于传输到服务器的 Id 和实际 JavaScript 代码。
Step 6 − First create a definition object with the Id for the new resource and then call one of the Create methods on the DocumentClient object. In the case of a stored procedure, the definition includes the Id and the actual JavaScript code that you want to ship over to the server.
Step 7 - 调用 File.ReadAllText 以从 JS 文件中提取存储过程代码。
Step 7 − Call File.ReadAllText to extract the stored procedure code out of the JS file.
Step 8 - 将存储过程代码分配给定义对象的 body 属性。
Step 8 − Assign the stored procedure code to the body property of the definition object.
对于 DocumentDB 来说，我们在定义中指定的 Id 是存储过程的名称，无关乎我们实际为 JavaScript 函数命名的名称。
As far as DocumentDB is concerned, the Id we specify here, in the definition, is the name of the stored procedure, regardless of what we actually name the JavaScript function.
然而，在创建存储过程和其他服务器端对象时，建议为 JavaScript 函数命名，而且这些函数名称要与我们在 DocumentDB 定义中设置的 Id 匹配。
Nevertheless when creating stored procedures and other server-side objects, it is recommended that we name JavaScript functions and that those function names do match the Id that we have set in the definition for DocumentDB.
Step 9 - 调用 CreateStoredProcedureAsync ，传递给 SelfLink 的 MyCollection 以及存储过程定义。这将创建 DocumentDB 为其分配的存储过程和 ResourceId 。
Step 9 − Call CreateStoredProcedureAsync, passing in the SelfLink for the MyCollection collection and the stored procedure definition. This creates the stored procedure and ResourceId that DocumentDB assigned to it.
Step 10 - 调用存储过程。 ExecuteStoredProcedureAsync 采用类型参数，将其设置为存储过程返回的值的预期数据类型，如果你想要返回一个动态对象，则可以简单地将其指定为一个对象。那是一个在运行时将绑定其属性的对象。
Step 10 − Call the stored procedure. ExecuteStoredProcedureAsync takes a type parameter that you set to the expected data type of the value returned by the stored procedure, which you can specify simply as an object if you want a dynamic object returned. That is an object whose properties will be bound at run-time.
在本例中，我们知道存储过程只返回一个字符串，因此我们调用 ExecuteStoredProcedureAsync<string> 。
In this example we know that our stored procedure is just returning a string and so we call ExecuteStoredProcedureAsync<string>.
以下是 Program.cs 文件的完整实现。
Following is the complete implementation of Program.cs file.
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DocumentDBStoreProce {
class Program {
private static void Main(string[] args) {
Task.Run(async () => {
await SimpleStoredProcDemo();
}).Wait();
}
private async static Task SimpleStoredProcDemo() {
var endpoint = "https://azuredocdbdemo.documents.azure.com:443/";
var masterKey =
"BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";
using (var client = new DocumentClient(new Uri(endpoint), masterKey)) {
// Get database
Database database = client
.CreateDatabaseQuery("SELECT * FROM c WHERE c.id = 'myfirstdb'")
.AsEnumerable()
.First();
// Get collection
DocumentCollection collection = client
.CreateDocumentCollectionQuery(database.CollectionsLink,
"SELECT * FROM c WHERE c.id = 'MyCollection'")
.AsEnumerable()
.First();
// Create stored procedure
var sprocBody = File.ReadAllText(@"..\..\HelloWorldStoreProce.js");
var sprocDefinition = new StoredProcedure {
Id = "HelloWorldStoreProce",
Body = sprocBody
};
StoredProcedure sproc = await client
.CreateStoredProcedureAsync(collection.SelfLink, sprocDefinition);
Console.WriteLine("Created stored procedure {0} ({1})", sproc
.Id, sproc.ResourceId);
// Execute stored procedure
var result = await client
.ExecuteStoredProcedureAsync<string>(sproc.SelfLink);
Console.WriteLine("Executed stored procedure; response = {0}",
result.Response);
// Delete stored procedure
await client.DeleteStoredProcedureAsync(sproc.SelfLink);
Console.WriteLine("Deleted stored procedure {0} ({1})",
sproc.Id, sproc.ResourceId);
}
}
}
}执行以上代码时，会产生以下输出。
When the above code is executed, it produces the following output.
Created stored procedure HelloWorldStoreProce (Ic8LAMEUVgACAAAAAAAAgA==)
Executed stored procedure; response = Hello, and welcome to DocumentDB!如上面的输出所示，response 属性包含存储过程返回的“Hello, and welcome to DocumentDB!”。
As seen in the above output, the response property has the “Hello, and welcome to DocumentDB!” returned by our stored procedure.
DocumentDB SQL - User-Defined Functions
DocumentDB SQL 提供对用户自定义函数 (UDF) 的支持。UDF 只是另一种你可以编写的 JavaScript 函数，且它们的工作方式几乎和你预期的一样。你可以创建 UDF 以使用可以在查询中引用的自定义业务逻辑来扩展查询语言。
DocumentDB SQL provides support for User-Defined Functions (UDFs). UDFs are just another kind of JavaScript functions you can write and these work pretty much as you’d expect. You can create UDFs to extend the query language with custom business logic that you can reference in your queries.
DocumentDB SQL 语法被扩展以使用这些 UDF 来支持自定义应用程序逻辑。UDF 可以向 DocumentDB 注册，然后被引用为 SQL 查询的一部分。
The DocumentDB SQL syntax is extended to support custom application logic using these UDFs. UDFs can be registered with DocumentDB and then be referenced as part of a SQL query.
为此示例，我们考虑以下三个文档。
Let’s consider the following three documents for this example.
AndersenFamily 文档如下。
AndersenFamily document is as follows.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}SmithFamily 文档如下。
SmithFamily document is as follows.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}WakefieldFamily 文档如下。
WakefieldFamily document is as follows.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}我们来看一个我们将创建一些简单 UDF 的示例。
Let’s take a look at an example where we will create some simple UDFs.
以下是 CreateUserDefinedFunctions 的实现。
Following is the implementation of CreateUserDefinedFunctions.
private async static Task CreateUserDefinedFunctions(DocumentClient client) {
Console.WriteLine();
Console.WriteLine("**** Create User Defined Functions ****");
Console.WriteLine();
await CreateUserDefinedFunction(client, "udfRegEx");
}我们有一个 udfRegEx，在 CreateUserDefinedFunction 中，我们从本地文件中获取其 JavaScript 代码。我们构造新 UDF 的定义对象，并使用集合的 SelfLink 和如下代码所示的 udfDefinition 对象调用 CreateUserDefinedFunctionAsync。
We have a udfRegEx, and in CreateUserDefinedFunction we get its JavaScript code from our local file. We construct the definition object for the new UDF, and call CreateUserDefinedFunctionAsync with the collection’s SelfLink and the udfDefinition object as shown in the following code.
private async static Task<UserDefinedFunction>
CreateUserDefinedFunction(DocumentClient client, string udfId) {
var udfBody = File.ReadAllText(@"..\..\Server\" + udfId + ".js");
var udfDefinition = new UserDefinedFunction {
Id = udfId,
Body = udfBody
};
var result = await client
.CreateUserDefinedFunctionAsync(_collection.SelfLink, udfDefinition);
var udf = result.Resource;
Console.WriteLine("Created user defined function {0}; RID: {1}",
udf.Id, udf.ResourceId);
return udf;
}我们从结果的 resource 属性中返回新的 UDF，并将其返回给调用者。为了显示现有的 UDF，以下是 ViewUserDefinedFunctions 的实现。我们调用 CreateUserDefinedFunctionQuery 并像往常一样遍历它们。
We get back the new UDF from the resource property of the result and return it back up to the caller. To display the existing UDF, following is the implementation of ViewUserDefinedFunctions. We call CreateUserDefinedFunctionQuery and loop through them as usual.
private static void ViewUserDefinedFunctions(DocumentClient client) {
Console.WriteLine();
Console.WriteLine("**** View UDFs ****");
Console.WriteLine();
var udfs = client
.CreateUserDefinedFunctionQuery(_collection.UserDefinedFunctionsLink)
.ToList();
foreach (var udf in udfs) {
Console.WriteLine("User defined function {0}; RID: {1}", udf.Id, udf.ResourceId);
}
}DocumentDB SQL 未提供用于搜索子字符串或正则表达式的内置函数，因此以下简洁的一行代码填补了缺口，它是一个 JavaScript 函数。
DocumentDB SQL doesn’t provide built-in functions to search for substrings or for regular expressions, hence the following little one-liner fills that gap which is a JavaScript function.
function udfRegEx(input, regex) {
return input.match(regex);
}鉴于第一个参数中的输入字符串，使用 JavaScript 的内置正则表达式支持，将第二个参数中的模式匹配字符串传递到 . match 中。我们可以运行一个子字符串查询，以在 lastName 属性中找到单词 Andersen 的所有商店。
Given the input string in the first parameter, use JavaScript’s built-in regular expression support passing in the pattern matching string in the second parameter into .match. We can run a substring query to find all stores with the word Andersen in their lastName property.
private static void Execute_udfRegEx(DocumentClient client) {
var sql = "SELECT c.name FROM c WHERE udf.udfRegEx(c.lastName, 'Andersen') != null";
Console.WriteLine();
Console.WriteLine("Querying for Andersen");
var documents = client.CreateDocumentQuery(_collection.SelfLink, sql).ToList();
Console.WriteLine("Found {0} Andersen:", documents.Count);
foreach (var document in documents) {
Console.WriteLine("Id: {0}, Name: {1}", document.id, document.lastName);
}
}请注意，我们必须使用前缀 udf 限定每个 UDF 引用。就像对任何普通查询一样，我们刚刚将 SQL 传递给 CreateDocumentQuery 。最后，让我们从 CreateDocumentClient 任务中调用以上查询
Note that we must qualify every UDF reference with the prefix udf. We just passed the SQL along to CreateDocumentQuery like any ordinary query. Finally, let’s call the above queries from the CreateDocumentClient task
private static async Task CreateDocumentClient() {
// Create a new instance of the DocumentClient
using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)){
database = client.CreateDatabaseQuery("SELECT * FROM c WHERE
c.id = 'myfirstdb'").AsEnumerable().First();
collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
"SELECT * FROM c WHERE c.id = 'Families'").AsEnumerable().First();
await CreateUserDefinedFunctions(client);
ViewUserDefinedFunctions(client);
Execute_udfRegEx(client);
}
}执行以上代码时，会产生以下输出。
When the above code is executed, it produces the following output.
**** Create User Defined Functions ****
Created user defined function udfRegEx; RID: kV5oANVXnwAlAAAAAAAAYA==
**** View UDFs ****
User defined function udfRegEx; RID: kV5oANVXnwAlAAAAAAAAYA==
Querying for Andersen
Found 1 Andersen:
Id: AndersenFamily, Name: AndersenDocumentDB SQL - Composite SQL Queries
Composite Query 使您可以组合来自现有查询的数据，然后再应用筛选器、聚合等，然后才能显示结果数据集中显示的组合数据。复合查询检索现有查询上与多级相关的信息，并将组合的数据作为单个扁平化查询结果呈现出来。
Composite Query enables you to combine data from existing queries and then apply filters, aggregates, and so on before presenting the report results, which show the combined data set. Composite Query retrieves multiple levels of related information on existing queries and presents the combined data as a single and flattened query result.
使用复合查询，您还可以选择：
Using Composite Query, you also have the option to −
-
Select the SQL pruning option to remove tables and fields that are not needed based on users’ attribute selections.
-
Set the ORDER BY and GROUP BY clauses.
-
Set the WHERE clause as a filter over the result set of a composite query.
可以组合上述运算符以形成功能更强大的查询。由于 DocumentDB 支持嵌套集合，因此可以级联或嵌套组合。
The above operators can be composed to form more powerful queries. Since DocumentDB supports nested collections, the composition can either be concatenated or nested.
让我们考虑一下演示本文档的以下内容。
Let’s consider the following documents for this example.
AndersenFamily 文档如下。
AndersenFamily document is as follows.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}SmithFamily 文档如下。
SmithFamily document is as follows.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}WakefieldFamily 文档如下。
WakefieldFamily document is as follows.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}让我们来看一个级联查询的示例。
Let’s take a look at an example of concatenated query.
以下是将检索第一个孩子 givenName 为 Michelle 的家庭的 id 和位置的查询。
Following is the query which will retrieve the id and location of the family where the first child givenName is Michelle.
SELECT f.id,f.location
FROM Families f
WHERE f.children[0].givenName = "Michelle"执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"id": "SmithFamily",
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
}
}
]让我们考虑级联查询的另一个示例。
Let’s consider another example of concatenated query.
以下是将返回所有第一个孩子成绩高于 3 的文档的查询。
Following is the query which will return all the documents where the first child grade greater than 3.
SELECT *
FROM Families f
WHERE ({grade: f.children[0].grade}.grade > 3)执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"id": "WakefieldFamily",
"parents": [
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Miller",
"givenName": "Ben"
}
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{
"givenName": "Charlie Brown",
"type": "Dog"
},
{
"givenName": "Tiger",
"type": "Cat"
},
{
"givenName": "Princess",
"type": "Cat"
}
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{
"givenName": "Jake",
"type": "Snake"
}
]
}
],
"location": {
"state": "NY",
"county": "Manhattan",
"city": "NY"
},
"isRegistered": false,
"_rid": "Ic8LAJFujgECAAAAAAAAAA==",
"_ts": 1450541623,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/",
"_etag": "\"00000500-0000-0000-0000-567582370000\"",
"_attachments": "attachments/"
},
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{
"firstName": "Thomas",
"relationship": "father"
},
{
"firstName": "Mary Kay",
"relationship": "mother"
}
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [
{
"givenName": "Fluffy",
"type": "Rabbit"
}
]
}
],
"location": {
"state": "WA",
"county": "King",
"city": "Seattle"
},
"isRegistered": true,
"_rid": "Ic8LAJFujgEEAAAAAAAAAA==",
"_ts": 1450541624,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/",
"_etag": "\"00000700-0000-0000-0000-567582380000\"",
"_attachments": "attachments/"
}
]让我们来看一个 example 嵌套查询。
Let’s take a look at an example of nested queries.
以下是将遍历所有父项然后返回 familyName 为 Smith 的文档的查询。
Following is the query which will iterate all the parents and then return the document where familyName is Smith.
SELECT *
FROM p IN Families.parents
WHERE p.familyName = "Smith"执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
[
{
"familyName": "Smith",
"givenName": "James"
}
]让我们考虑 another example 嵌套查询。
Let’s consider another example of nested query.
以下是将返回所有 familyName 的查询。
Following is the query which will return all the familyName.
SELECT VALUE p.familyName
FROM Families f
JOIN p IN f.parents执行以上查询后，会生成以下输出。
When the above query is executed, it produces he following output.
[
"Wakefield",
"Miller",
"Smith",
"Curtis"
]