An operator is a reserved word or a man or woman used in the main in an SQL WHERE clause to carry out operation(s), which include comparisons and arithmetic operations. DocumentDB SQL also supports a spread of scalar expressions. The maximum normally used are binary and unary expressions.
The following SQL operators are presently supported and may be utilized in queries.
SQL Comparison Operators
Following is a listing of all the comparison operators available in DocumentDB SQL grammar.
S.No. | Operators & Description |
---|---|
1 |
AND The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause. |
2 |
BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. |
3 |
IN The IN operator is used to compare a value to a list of literal values that have been specified. |
4 |
OR The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause. |
5 |
NOT The NOT operator reverses the meaning of the logical operator with which it is used. For example, NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. |
SQL Logical Operators
Following is a list of all the logical operators available in DocumentDB SQL grammar.
S.No. | Operators & Description |
---|---|
1 |
AND The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause. |
2 |
BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. |
3 |
IN The IN operator is used to compare a value to a list of literal values that have been specified. |
4 |
OR The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause. |
5 |
NOT The NOT operator reverses the meaning of the logical operator with which it is used. For example, NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. |
SQL Arithmetic Operators
Following is a list of all the arithmetic operators available in DocumentDB SQL grammar.
S.No. | Operators & Description |
---|---|
1 |
+ Addition − Adds values on either side of the operator. |
2 |
- Subtraction − Subtracts the right hand operand from the left hand operand. |
3 |
* Multiplication − Multiplies values on either side of the operator. |
4 |
/ Division − Divides the left hand operand by the right hand operand. |
5 |
% Modulus − Divides the left hand operand by the right hand operand and returns the remainder. |
We will consider the same documents in this example as well. 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 a comparison operator is used in WHERE clause.
In this query, in WHERE clause, the (WHERE f.Id = "WakefieldFamily") condition is specified, and it will retrieve the document whose id is equal to WakefieldFamily.
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/"
}
]
Let’s take a look at another example in which the query will retrieve the children data whose grade is greater than 5.
SELECT *
FROM Families.children[0] c
WHERE (c.grade > 5)
When the above query is accomplished, it'll retrieve the following sub document as proven inside the output.
[
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{
"givenName": "Charlie Brown",
"type": "Dog"
},
{
"givenName": "Tiger",
"type": "Cat"
},
{
"givenName": "Princess",
"type": "Cat"
}
]
}
]