Aggregation query in Cosmos DB

Aggregation Queries in Cosmos DB with Ternary: A Workaround for Performance Concerns
Due to potential performance issues, I understand it's not typical to write aggregation queries like SUM and AVG on NoSQL databases. However, sometimes it's necessary to find a workaround for temporary situations. With my extensive experience in database development and providing SQL reports in operational and data warehouse databases, I know how useful aggregation queries can be.

In my special case, I needed a combination of SUM and CASE, common in Oracle and SQL Server databases. For example:

SELECT SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved FROM orders;

However, Cosmos DB does not support the CASE operation. Instead, it introduces the Ternary operator. Ternary works like iif :

<bool_expr> ?
<expr_true> :
<expr_false>

Here's how I constructed my query to get the sum of orders for today and this month:

SELECT
    SUM((c.orderDate > '{yesterday:O}' AND c.orderDate <= '{today:O}') ? c.price : 0) AS todayPrice,
    SUM((c.orderDate >= '{thisMonthStart:O}' AND c.orderDate < '{thisMonthStart.AddMonths(1):O}') ? c.price : 0) AS thisMonthPrice
FROM c

Explanation:

• Ternary Operator: Used to replace the CASE statement in Cosmos DB.
• todayPrice: Sums the prices of orders placed today.
• thisMonthPrice: Sums the prices of orders placed this month. I eliminate real date calue but you can provide them if you need a direct query or use placeholders if you run the code from an API.