edu Toidayhoc

Category: documentdb Sql

  • Khóa học miễn phí DocumentDB SQL – Where Clause nhận dự án làm có lương

    DocumentDB SQL – Where Clause

    In this chapter, we will cover the WHERE clause, which is also optional like FROM clause. It is used to specify a condition while fetching the data in the form of JSON documents provided by the source. Any JSON document must evaluate the specified conditions to be “true” to be considered for the result. If the given condition is satisfied, only then it returns specific data in the form of JSON document(s). We can use WHERE clause to filter the records and fetch only necessary records.

    We will consider the same three documents in this example. 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 in which WHERE clause is used.

    WHERE Clause

    In this query, in WHERE clause, the (WHERE f.id = “WakefieldFamily”) condition is specified.

    SELECT *
FROM f
WHERE f.id = "WakefieldFamily"

    When the above query is executed, it will return the complete JSON document for WakefieldFamily as shown in the following output.

    [
   {
      "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,
      "_rid": "Ic8LAJFujgECAAAAAAAAAA==",
      "_ts": 1450541623,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/",
      "_etag": ""00000500-0000-0000-0000-567582370000"",
      "_attachments": "attachments/"
   }
]

    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 – Select Clause nhận dự án làm có lương

    DocumentDB SQL – Select Clause

    The Azure portal has a Query Explorer that lets us run any SQL query against our DocumentDB database. We will use the Query Explorer to demonstrate the many different capabilities and features of the query language starting with the simplest possible query.

    Step 1 − Open the Azure Portal, and in the database blade, click the Query Explorer blade.

    Query Explorer Blade

    Remember that queries run within the scope of a collection, and so the Query Explorer lets us choose the collection in this dropdown. We will leave it set to our Families collection that contains the three documents. Let’s consider these three documents in this example.

    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
}

    The Query Explorer opens up with this simple query SELECT * FROM c, which simply retrieves all documents from the collection. Although it is simple, it”s still quite different than the equivalent query in a relational database.

    Step 2 − In relational databases, SELECT * means return all columns while in DocumentDB. It means that you want each document in your result to be returned exactly as it”s stored in the database.

    But when you select specific properties and expressions instead of simply issuing a SELECT *, then you are projecting a new shape that you want for each document in the result.

    Step 3 − Click ‘Run’ to execute query and open the Results blade.

    Open Results Blade

    As can be seen the WakefieldFamily, the SmithFamily, and the AndersonFamily are retrieved.

    Following are the three documents which are retrieved as a result of the SELECT * FROM c query.

    [
   {
      "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,
      "_rid": "Ic8LAJFujgECAAAAAAAAAA==",
      "_ts": 1450541623,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/",
      "_etag": ""00000500-0000-0000-0000-567582370000"",
      "_attachments": "attachments/"
   },

   {
      "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,
      "_rid": "Ic8LAJFujgEDAAAAAAAAAA==",
      "_ts": 1450541623,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEDAAAAAAAAAA==/",
      "_etag": ""00000600-0000-0000-0000-567582370000"",
      "_attachments": "attachments/"
   },

   {
      "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,
      "_rid": "Ic8LAJFujgEEAAAAAAAAAA==",
      "_ts": 1450541624,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/",
      "_etag": ""00000700-0000-0000-0000-567582380000"",
      "_attachments": "attachments/"
   }
]

    However, these results also include the system-generated properties that are all prefixed with the underscore character.


    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 – Between Keyword nhận dự án làm có lương

    DocumentDB SQL – Between Keyword

    The BETWEEN keyword is used to express queries against ranges of values like in SQL. BETWEEN can be used against strings or numbers. The main difference between using BETWEEN in DocumentDB and ANSI SQL is that you can express range queries against properties of mixed types.

    For example, in some document it is possible that you might have “grade” as a number and in other documents it might be strings. In these cases, a comparison between two different types of results is “undefined”, and the document will be skipped.

    Let us consider the three documents from the previous example. 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, where the query returns all family documents in which the first child”s grade is between 1-5 (both inclusive).

    Returns Family Documents

    Following is the query in which BETWEEN keyword is used and then AND logical operator.

    SELECT *
FROM Families.children[0] c
WHERE c.grade BETWEEN 1 AND 5

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

    [
   {
      "givenName": "Michelle",
      "gender": "female",
      "grade": 1
   },

   {
      "firstName": "Henriette Thaulow",
      "gender": "female",
      "grade": 5,

      "pets": [
         {
            "givenName": "Fluffy",
            "type": "Rabbit"
         }
      ]
   }
]

    To display the grades outside the range of the previous example, use NOT BETWEEN as shown in the following query.

    SELECT *
FROM Families.children[0] c
WHERE c.grade NOT BETWEEN 1 AND 5

    When this query is executed. It produces the following output.

    [
   {
      "familyName": "Merriam",
      "givenName": "Jesse",
      "gender": "female",
      "grade": 6,

      "pets": [
         {
            "givenName": "Charlie Brown",
            "type": "Dog"
         },

         {
            "givenName": "Tiger",
            "type": "Cat"
         },

         {
            "givenName": "Princess",
            "type": "Cat"
         }
      ]
   }
]

    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 – In Keyword nhận dự án làm có lương

    DocumentDB SQL – In Keyword

    The IN keyword can be used to check whether a specified value matches any value in a list. The IN operator allows you to specify multiple values in a WHERE clause. IN is equivalent to chaining multiple OR clauses.

    The similar three documents are considered as done in earlier 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 a simple example.

    In KeyWord

    Following is the query which will retrieve the data whose familyName is either “Smith” or Wakefield.

    SELECT *
FROM Families.parents[0] f
WHERE f.familyName IN (''Smith'', ''Wakefield'')

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

    [
   {
      "familyName": "Wakefield",
      "givenName": "Robin"
   },

   {
      "familyName": "Smith",
      "givenName": "James"
   }
]

    Let’s consider another simple example in which all family documents will be retrieved where the id is one of “SmithFamily” or “AndersenFamily”. Following is the query.

    SELECT *
FROM Families
WHERE Families.id IN (''SmithFamily'', ''AndersenFamily'')

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

    [
   {
      "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,
      "_rid": "Ic8LAJFujgEDAAAAAAAAAA==",
      "_ts": 1450541623,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEDAAAAAAAAAA==/",
      "_etag": ""00000600-0000-0000-0000-567582370000"",
      "_attachments": "attachments/"
   },

   {
      "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,
      "_rid": "Ic8LAJFujgEEAAAAAAAAAA==",
      "_ts": 1450541624,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/",
      "_etag": ""00000700-0000-0000-0000-567582380000"",
      "_attachments": "attachments/"
   }
]

    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