Documentdb Sql 简明教程
DocumentDB 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"
]