Azure Cosmos Db – Part four- Querying the Document DB
Querying the DocumentDb
DocumentDb offers a rich T-SQL like query experience which allow us to query it using the SELECT, FROM and Where Query. It gives all the following queries:
- Filtering
As we know filtering refers to refining the data and take out the minimum required data based on the user requirement. If you’re familiar with T-SQL, we use select, from and where Clause.
Let’s us try to see a query where collection name is employee and we need to take out the id, designation, location and BloodGroup from the record and where the employee group is Developers and designation is Software Engineer.
SELECT Employee.id,
Employee.desigination,
Employee.location,
Employee.BloodGroup
FROM Employee
WHERE Employee.Group = “Developers” and Employee.desigination = “Software Engineer”
2.Sorting
Sorting refers to the rearranging the records based on some property in an ascending or descending order. We use the ORDER BY clause on some property and tell it to sort it. Here we are selecting the Employee designation,location and BloodGroup where the Employee Blood Group is A+ and we are sorting the Records on the basis of the Employee Name in ascending order.
SELECT Employee. desigination,
Employee. location,
Employee. BloodGroup AS BG
FROM Employee
WHERE Employee.EmployeeGroup = “A+”
ORDER BY Employee.Name Asc
3.Top
TOP refers to taking out N no of Top records from the Database based on the query. Here we are taking out the top 20 records where Blood Group is B+.
SELECT TOP 20 Employee.id,
Employee.description,
Employee. location,
Employee. BloodGroup
FROM Employee
WHERE Employee. BloodGroup= “B+”
4.In & Between
The BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
SELECT Employee.id,
Employee.description,
Employee. location,
Employee. BloodGroup,
FROM Employee
WHERE
Employee.id BETWEEN “05740” AND “07050”
5.Projection
Projection means casting the property into new project JSON Object with modified property names.
SELECT {
“Name”: Employee.Name,
“Role”: Employee.Designation,
“Employee Blood Group”: Employee.BloodGroup
} AS Employee
FROM Employee
WHERE Employee.id = “21421”
6.Joins
Azure Cosmos DB’s JOIN supports intra-document and self-joins
SELECT tag.name
FROM Employee
JOIN tag IN Employee.tags
WHERE Employee.id = “09052”
7.Built-in
Azure Cosmos DB supports a number of built-in functions for common operations. They cover mathematical functions like ABS, FLOOR and ROUND and type checking functions like IS_ARRAY, IS_BOOL and IS_DEFINED. To find a full list of supported built-in functions, head over to our query page. Run the query below to see these built-in functions in action.
8.UDF
UDF means User Defined Functions. UDFs can be written in Javascript, then used in SQL Statements. and we will cover how to write a UDF in a separate article. Here just need to understand that how we’re calling the function. Here udf.MATCH is a function which is going to match
SELECT Employee.id,
Employee.description,
Employee.tags,
Employee.BloodGroup
FROM Employee
WHERE Employee.BloodGroup = “B+” AND udf.MATCH(Employee.Designation, “Software-Engineer, Senior Software-Engineer”) != null
9.GeoSpatial
Spatial data describes the position and shape of objects in space. In most applications, these correspond to objects on the earth, i.e. geospatial data. Spatial data can be used to represent the location of a person, a place of interest, or the boundary of a city, or a lake. Common use cases often involve proximity queries, for e.g., “find all coffee shops near my current location”. Continuing with the Employee example we are looking for employee whose home location is near by the coordinates defined.
SELECT *
FROM Employee E
WHERE ST_DISTANCE(E.Location, {
“type”: “Point”,
“coordinates”: [-122.19, 47.36]
}) < 100 * 1000
Summary
Document Db is a no Sql Db but it gives all the T-SQL like query experience. You can run the query on the azure portal or run through the client SDK. It gives multiple options like Filtering, Sorting, Top, in & Between, Built in, UDF, Joins and Geo Spatial queries.
With thanks! Valuable information!
With thanks! Valuable information!