Documentdb Sql 简明教程
DocumentDB SQL - Composite SQL Queries
Composite Query 使您可以组合来自现有查询的数据,然后再应用筛选器、聚合等,然后才能显示结果数据集中显示的组合数据。复合查询检索现有查询上与多级相关的信息,并将组合的数据作为单个扁平化查询结果呈现出来。
使用复合查询,您还可以选择:
-
选择 SQL 修剪选项,以基于用户的属性选择删除不需要的表和字段。
-
设置 ORDER BY 和 GROUP BY 子句。
-
将 WHERE 子句作为一个筛选器设置在复合查询的结果集中。
可以组合上述运算符以形成功能更强大的查询。由于 DocumentDB 支持嵌套集合,因此可以级联或嵌套组合。
让我们考虑一下演示本文档的以下内容。
AndersenFamily 文档如下。
{
"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 文档如下。
{
"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 文档如下。
{
"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
}
让我们来看一个级联查询的示例。
以下是将检索第一个孩子 givenName 为 Michelle 的家庭的 id 和位置的查询。
SELECT f.id,f.location
FROM Families f
WHERE f.children[0].givenName = "Michelle"
执行以上查询后,将产生以下输出。
[
{
"id": "SmithFamily",
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
}
}
]
让我们考虑级联查询的另一个示例。
以下是将返回所有第一个孩子成绩高于 3 的文档的查询。
SELECT *
FROM Families f
WHERE ({grade: f.children[0].grade}.grade > 3)
执行以上查询后,将产生以下输出。
[
{
"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 嵌套查询。
以下是将遍历所有父项然后返回 familyName 为 Smith 的文档的查询。
SELECT *
FROM p IN Families.parents
WHERE p.familyName = "Smith"
执行以上查询后,将产生以下输出。
[
{
"familyName": "Smith",
"givenName": "James"
}
]
让我们考虑 another example 嵌套查询。
以下是将返回所有 familyName 的查询。
SELECT VALUE p.familyName
FROM Families f
JOIN p IN f.parents
执行以上查询后,会生成以下输出。
[
"Wakefield",
"Miller",
"Smith",
"Curtis"
]