在DocumentDB SQL中,Microsoft添加了一个新的构造,可以与IN关键字一起使用,以支持迭代JSON数组. FROM子句中提供了对迭代的支持.
我们将再次考虑前面示例中的类似三个文档.
以下是 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 }
让我们来看一个简单的FROM子句中没有IN关键字的示例.
以下是查询将返回Families集合中的所有父项.
SELECT * FROM Families.parents
执行上述查询时,它会产生以下输出.
[ [ { "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数组中.
让我们看看同一个例子,但是这次我们将在FROM子句中使用IN关键字.
以下是包含IN关键字的查询.
SELECT * FROM c IN Families.parents
执行上述查询时,它会生成以下输出.
[ { "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 } ]
在上面的例子中,可以看到迭代时,执行迭代的查询集合有不同的输出数组.因此,每个家庭的所有父母都被添加到一个阵列中.