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:

1. 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.

Comments

comments