# Data retrieval in Cosmos DB

In an [previous article](https://www.azureguru.net/crud-operations-in-cosmos-db) 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](https://www.azureguru.net/crud-operations-in-cosmos-db) to test the queries.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725039059262/564a81dc-47b4-4c49-87a3-90d401d05617.png align="center")

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

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725039287718/41b2e375-fce3-48f4-abaf-311e0ffe993d.png align="center")

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
    

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725042879575/8a1aebf9-1be2-4219-af94-a9f16de0a78d.png align="center")

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

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725042019294/0d7830f9-7ecd-411f-9786-64d80828c4b6.png align="center")

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

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725040584025/95e99af5-bd68-4502-bdf0-d33bcc95c00c.png align="center")

4. Fetch all `SupplierName` and the `PastQuantity` values.
    

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725042974999/214f8502-0ff0-45a0-ab34-f6c744608d45.png align="center")

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

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725042515719/fcc7123b-7f22-4022-b4f5-882dabbf49c7.png align="center")

6. Lets look into few aggregate functions.
    

Query to count the number of suppliers of each product

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725042742868/d82a26d5-e06f-4276-80d5-4caf88aeb32f.png align="center")

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.
    

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725043119592/a14160d7-0bdf-4ff5-85df-ae706d6f9b94.png align="center")

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.

```javascript
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.

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1725047722078/12ea40de-79c9-4b26-85d3-7b7117784c86.png align="center")

There is a repository of sample ComsosDB queries and functions [here](https://github.com/Azure-Samples/cosmos-db-nosql-query-samples/tree/main/scripts) .  
  
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.