This content originally appeared on DEV Community and was authored by Seifolah Ghaderi
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. 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.
This content originally appeared on DEV Community and was authored by Seifolah Ghaderi
Seifolah Ghaderi | Sciencx (2024-06-26T17:40:40+00:00) Aggregation query in Cosmos DB. Retrieved from https://www.scien.cx/2024/06/26/aggregation-query-in-cosmos-db/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.