In relational databases, the Joins clause is used to combine statistics from two or greater tables in a database, and the need to sign up for throughout tables could be very crucial at the same time as designing normalized schemas. Since DocumentDB offers with the denormalized information version of schema-unfastened files, the JOIN in DocumentDB SQL is the logical equal of a "selfjoin".
Let’s remember the 3 documents as within the previous examples.
Following is the AndersenFamily file.
{
"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
}
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
}
Following is the WakefieldFamily file.
{
"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
}
Let’s take a look at an instance to recognize how the JOIN clause works.
Following is the question that allows you to be a part of the root to kids subdocument.
SELECT f.id
FROM Families f
JOIN c IN f.children
When the above question is executed, it'll produce the subsequent output.
[
{
"id": "WakefieldFamily"
},
{
"id": "WakefieldFamily"
},
{
"id": "SmithFamily"
},
{
"id": "SmithFamily"
},
{
"id": "AndersenFamily"
}
]
In the above instance, the be part of is between the file root and the youngsters sub-root which makes a move-product among JSON items. Following are sure factors to observe −
- In the FROM clause, the JOIN clause is an iterator.
- The first documents WakefieldFamily and SmithFamily comprise youngsters, therefore the result set also carries the move-product which produces a separate item for each toddler.
- The 0.33 report AndersenFamily incorporates handiest one children, consequently there is only a unmarried object similar to this file.
Let’s check the identical instance, however this time we retrieve the kid call as properly for higher expertise of JOIN clause.
Following is the question for you to be a part of the foundation 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 question is completed, 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"
}
]