在关系数据库中,Joins子句用于组合数据库中两个或多个表的记录,在设计规范化模式时,跨表连接的需求非常重要.由于DocumentDB处理无模式文档的非规范化数据模型,因此DocumentDB SQL中的JOIN是"selfjoin"的逻辑等价物.
让我们考虑前面示例中的三个文档.
以下是 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 }
以下是取值mithFamily 文件.
{ "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 }
让我们看一个例子来了解JOIN子句的工作原理.
以下是将根连接到子子文档的查询.
SELECT f.id FROM Families f JOIN c IN f.children
执行上述查询时,它将产生以下输出.
[ { "id": "WakefieldFamily" }, { "id": "WakefieldFamily" }, { "id": "SmithFamily" }, { "id": "SmithFamily" }, { "id": "AndersenFamily" } ]
在上面的例子中,联接在文档根目录和子子根之间进行交叉两个JSON对象之间的s-product.以下是需要注意和减去的特定要点;
在FROM子句中,JOIN子句是一个迭代器.
前两个文档WakefieldFamily和SmithFamily包含两个子节点,因此结果集还包含为每个子节点生成单独对象的交叉产品.
第三个文档AndersenFamily只包含一个子文档,因此只有一个对象与此文档对应.
让我们看看同一个例子,但这次我们检索子名称以便更好地理解JOIN子句.
以下是将根连接到子子文档的查询.
SELECT f.id AS familyName, c.givenName AS childGivenName, c.firstName AS childFirstName FROM Families f JOIN c IN f.children
执行上述查询时,会产生以下输出.
[ { "familyName": "WakefieldFamily", "childGivenName": "Jesse" }, { "familyName": "WakefieldFamily", "childGivenName": "Lisa" }, { "familyName": "SmithFamily", "childGivenName": "Michelle" }, { "familyName": "SmithFamily", "childGivenName": "John" }, { "familyName": "AndersenFamily", "childFirstName": "Henriette Thaulow" } ]