Documentdb Sql 简明教程

DocumentDB SQL - Joins

在关系型数据库中，Join 子句用于合并数据库中来自两个或更多表的记录，并且在设计归一化架构时，非常需要连接跨表。由于 DocumentDB 处理无架构文档的非规范化数据模型，因此 DocumentDB SQL 中的 JOIN 是“selfjoin”的逻辑等效。

In relational databases, the Joins clause is used to combine records from two or more tables in a database, and the need to join across tables is very important while designing normalized schemas. Since DocumentDB deals with the denormalized data model of schema-free documents, the JOIN in DocumentDB SQL is the logical equivalent of a "selfjoin".

让我们考虑一下前一个示例中的三个文档。

Let’s consider the three documents as in the previous examples.

以下是 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
}

我们来看一个示例，以了解 JOIN 子句的工作原理。

Let’s take a look at an example to understand how the JOIN clause works.

以下是将根连接到子文档的查询。

Following is the query which will join the root to children subdocument.

SELECT f.id
FROM Families f
JOIN c IN f.children

执行上述查询时，将会产生以下输出。

When the above query is executed, it will produce the following output.

[
   {
      "id": "WakefieldFamily"
   },

   {
      "id": "WakefieldFamily"
   },

   {
      "id": "SmithFamily"
   },

   {
      "id": "SmithFamily"
   },

   {
      "id": "AndersenFamily"
   }
]

在上述示例中，连接在文档根和子根之间，在两个 JSON 对象之间生成笛卡尔积。以下是需要注意的几个要点 -

In the above example, the join is between the document root and the children sub-root which makes a cross-product between two JSON objects. Following are certain points to note −

In the FROM clause, the JOIN clause is an iterator.
The first two documents WakefieldFamily and SmithFamily contain two children, hence the result set also contains the cross-product which produces a separate object for each child.
The third document AndersenFamily contains only one children, hence there is only a single object corresponding to this document.

让我们来看相同的示例，但这一次，我们还检索子名称，以便更好地理解 JOIN 子句。

Let’s take a look at the same example, however this time we retrieve the child name as well for better understanding of JOIN clause.

以下是将根连接到子文档的查询。

Following is the query which will join the root to children subdocument.

SELECT
   f.id AS familyName,
   c.givenName AS childGivenName,
   c.firstName AS childFirstName
FROM Families f
JOIN c IN f.children

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

[
   {
      "familyName": "WakefieldFamily",
      "childGivenName": "Jesse"
   },

   {
      "familyName": "WakefieldFamily",
      "childGivenName": "Lisa"
   },

   {
      "familyName": "SmithFamily",
      "childGivenName": "Michelle"
   },

   {
      "familyName": "SmithFamily",
      "childGivenName": "John"
   },

   {
      "familyName": "AndersenFamily",
      "childFirstName": "Henriette Thaulow"
   }
]