Data retrieval in Cosmos DB

In an previous article I delved through process of inserting/updating/deleting documents in Cosmos DB.

The process of data retrieval from documents can be a little tricky in Cosmos DB as the retrieval process needs to traverse across multiple array objects and data elements that are intertwined. In this article be would look into queries to perform basic selections, filtering, aggregations and user-defined functions. So lets get started...

I would use the same sample documents that was created in the previous article to test the queries.

1. Lets start with a simple basic select query that fetches a Product named 'Product1' from the document

SELECT * from c where c.ProductName='product1'

2. Fetch all the suppliers along with products they supply. As the Suppliers are in a separate array object we would have to use a IN clause to fetch their values

SELECT a.ProductName,b.SupplierName,b.SupplierCity FROM a 
JOIN b IN a.Suppliers

You could add a predicate to filter for a specific Supplier. For example something like

SELECT a.ProductName,b.SupplierName,b.SupplierCity FROM a 
JOIN b IN a.Suppliers WHERE b.SupplierName = "SupplierForProductOne"

3. Fetch SupplierName and ProductName where the Suppliercity is "SupplierCity2".Similar to the previous query we would use the IN clause.

SELECT s.SupplierName,c.ProductName FROM c 
JOIN s IN c.Suppliers WHERE s.SupplierCity= "SupplierCity2"

4. Fetch all SupplierName and the PastQuantity values.

SELECT s.SupplierName,c.PastQuantity FROM c JOIN s IN c.Suppliers

5. This is an interesting one. Filter for a specific value of PastQuantity and return all the other values of PastQuantity from the same array. The query is filtering for the PastQuantity value of 14.

SELECT c.ProductName, s.SupplierName,c.PastQuantity FROM c 
JOIN s IN c.Suppliers WHERE ARRAY_CONTAINS(c.PastQuantity, "14")

6. Lets look into few aggregate functions.

Query to count the number of suppliers of each product

SELECT c.ProductName,COUNT(s.SupplierName) AS SupplierCount FROM c 
JOIN s IN c.Suppliers GROUP BY c.ProductName

7. Here we get the SUM of PastQuantity value across each product. As the PastQuantity value is of type string I have used stringtonumber function.

SELECT c.ProductName, SUM(stringtonumber(p)) AS TotalPastQuantity FROM c 
JOIN p IN c.PastQuantity GROUP BY c.ProductName

8. Cosmos DB supports UDF(User Defined Functions) that could be leveraged for complex data calculations and aggregations.

Lets create an UDF that sums up PastQuantity and Quantity.Note that PastQuantity is an array object in the document. So the function should iterate to sum the PastQuantity value.

function TotalQuantity(pastQuantityArray, quantity) 
{
var sum = quantity;
for (var i = 0; i < pastQuantityArray.length; i++) 
{ sum += parseInt(pastQuantityArray[i], 10) }
return sum; 
}

Query that uses the above UDF.

SELECT c.ProductName, 
udf.TotalQuantity(c.PastQuantity, c.Quantity) 'TotalQuantity',
c.PastQuantity from c

There is a repository of sample ComsosDB queries and functions here .

That's all folks !!!

Closing Notes

I hope the above queries would provide you with a good starting point for your data retrieval operations in Cosmos DB. While Cosmos DB uses a variant of SQL for querying documents there are some important and subtle differences to be aware of, which I’ve tried to highlight above.