Introduction to Azure Cosmos DB SQL Queries

     CosmosDB's SQL API uses T-SQL like language to query the database. It looks like T-SQL language. You still need to use SELECT, FROM, WHERE clause to retrieve the data you are looking for. It looks similar but it works different because the data structure is different. CosmosDB is not a relational database that means you cannot join other containers.

     Let's start with SELECT. You can use * to select all properties but just like T-SQL you shouldn't, pick only what you need. Charge of a Request Units depends on how much data your queries retrieves from containers. ORMs like to select everything and that's one of the reasons DBAs don't like them. I know couple of ORM supports CosmosDB. Think twice if you want to use an ORM with CosmosDB. Don't let an ORM to generate a query for CosmosDB. You will literally pay for it!

     For the demos in this post, I am going to use the following document.

{ 

 "id": "10", 
 "OrderId": 10, 
 "OrderDate": "2013-01-01T00:00:00.0000000", 
 "ExpectedDeliveryDate": "2013-01-02T00:00:00.0000000", 
 "CustomerID": 191, 
     "Customer": { 
       "CustomerName": "Tailspin Toys (Orchard Hill, GA)", 
       "CreditLimit": null, 
       "AccountOpenedDate": "2013-01-01T00:00:00.0000000", 
       "IsOnCreditHold": false, 
       "Address1": "Suite 169", 
       "Address2": "946 Reddy Street", 
       "Zipcode": "90698" 

       }, 

     "SalesPerson": { 
       "PersonId": 20, 
       "FullName": "Jack Potter", 
       "PhoneNumber": "(415) 555-0102", 
       "IsExternalProvider": false, 
       "Email": "jackp@wideworldimporters.com" 

}

Properties are case-sensitive, here is an example of a simple SELECT statement.

SELECT o.CustomerID, o.OrderDate
FROM Orders o 
WHERE o.OrderId = 10

[ 
 { 
   "CustomerID": 191, 
   "OrderDate": "2013-01-01T00:00:00.0000000" 
 } 
]

What if you want to return only the value rather than JSON. You can use VALUE Argument for that.

SELECT VALUE o.CustomerID
FROM Orders o 
WHERE o.OrderId = 10

[ 
 191
]

     JSON property names might include special characters, spaces. You can still query those properties by using Quoted Property Accessor. This works just like Javascript.

SELECT o["CustomerID"], o["OrderDate"]
FROM Orders o 
WHERE o.OrderId = 10

[ 
 { 
   "CustomerID": 191, 
   "OrderDate": "2013-01-01T00:00:00.0000000" 
 } 
]

You can return custom JSON object.

SELECT { "OrderedOn" : o.OrderDate, "DeliverOn" : o.ExpectedDeliveryDate} Custom 
FROM Orders o 
WHERE o.OrderId = 10

[ 
 { 
    "Custom": 
    { 
      "OrderedOn": "2013-01-01T00:00:00.0000000",
      "DeliverOn": "2013-01-02T00:00:00.0000000" 
    } 
 }
]

JOINS

     You cannot join other documents. You can join to other sub roots of current document. After using JOIN all that years in SQL Server, it took some time for me to understand how JOIN works in CosmosDB. Here is a simple example how it works. In the following example, I make a JOIN to Customer object to get some its properties

SELECT o.OrderId, c.CustomerName, c.Zipcode 
FROM Orders o 
JOIN o.Customer c 
WHERE o.OrderId = 10

[ 
 { 
   "OrderId": 10, 
   "CustomerName": "Tailspin Toys (Orchard Hill, GA)",
   "Zipcode": "90698" 
 } 
]

?: and ?? operators

     I use Null Coalescing operator and conditional operator frequently in C# and Javascript. You can use these two operators in CosmosDB queries too. Here is an example using ? operator.

SELECT (o.Customer.CreditLimit != null) ? o.Customer.CreditLimit : "No Limit" AS UserLimit 
FROM Orders o 
WHERE o.OrderId = 10

     ?? operator returns the value of a property if property exists in a document. If property does not exist, you can return another property just like the following example. SoldBy does not exist in JSON document. In this case, I return the FullName

SELECT o.SalesPerson.SoldBy ?? o.SalesPerson.FullName AS SoldBy 
FROM Orders o 
WHERE o.OrderId = 10