Documentdb Sql 简明教程
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.