Your cart is currently empty!
Author: alien
-
Khóa học miễn phí DocumentDB SQL – Aliasing nhận dự án làm có lương
DocumentDB SQL – Aliasing
In relational databases, SQL aliases are used to temporarily rename a table or a column heading. Similarly, in DocumentDB, aliases are used to temporarily rename a JSON document, sub-document, object or any field.
The renaming is a temporary change and the actual document does not change. Basically, aliases are created to make field/document names more readable. For aliasing, AS keyword is used which is optional.
Let’s consider three similar documents from the ones used in previous examples.
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 }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 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 }Let’s take a look at an example to discuss the aliases.
Following is the query which will join the root to children subdocument. We have aliases such as f.id AS familyName, c.givenName AS childGivenName, and c.firstName AS childFirstName.
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" } ]The above output shows that the filed names are changed, but it is a temporary change and the original documents are not modified.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí DocumentDB SQL – Iteration nhận dự án làm có lương
DocumentDB SQL – Iteration
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.
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
}
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 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
}
Let’s take a look at a simple example without IN keyword in FROM clause.
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"
}
]
]
As can be seen in the above output, the parents of each family is displayed in a separate JSON array.
Let’s take a look at the same example, however this time we will use the IN keyword in FROM clause.
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.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
