Learn how to work with aggregate functions in this article by Gastón C. Hillar, an independent consultant, a freelance author, and a speaker who has been working with computers since he was 8 years old and Daron Yöndem, a Microsoft Regional Director and a Microsoft MVP for 11 years.
— post by Gastón C. Hillar and Daron Yöndem, provided by Packt —
Working with aggregate functions
Cosmos DB SQL provides support for aggregations in the SELECT clause. For example, the following query will use the SUM aggregate function to sum all the values in the expression and calculate the total number of levels in the filtered games. The query uses the ARRAY_LENGTH built-in function to calculate the length of the levels array for each game and use it as an argument for the SUM aggregate function.
The code file for this article can be found at https://bit.ly/2FB9DDg. The sample is included in the sql_queries/videogame_1_17.sql file:
|
1 2 |
SELECT SUM(ARRAY_LENGTH(v.levels)) FROM Videogames v |
The following lines show the results of the query. Notice that the element of the array includes a key named $1:
|
1 2 3 4 5 |
[ { "$1": 6 } ] |
Whenever you use an expression in the SELECT clause that is not a property name and you don’t specify the desired alias name, Cosmos DB generates a key that starts with the $ prefix and continues with a number that starts in 1. Hence, if you have three expressions that aren’t property names and don’t include their desired aliases, Cosmos DB will use $1, $2 and $3 for the properties in the output results.
If you only want to generate the value without a key in the result, you can use the VALUE keyword. The following query uses this keyword. The code file for the sample is included in sql_queries/videogame_1_18.sql file:
|
1 2 |
SELECT VALUE SUM(ARRAY_LENGTH(v.levels)) FROM Videogames v |
The following lines show the results of the query. Notice that the element of the array doesn’t include the key:
|
1 2 3 |
[ 6 ] |
It is also possible to achieve the same goal using the COUNT aggregate function combined with the In keyword. The following query uses the COUNT aggregate function to count the number of items in the expression and calculate the total number of levels in the iterated levels for all the games. The code file for the sample is included in the sql_queries/videogame_1_19.sql file:
|
1 2 |
SELECT COUNT(l) AS totalNumberOfLevels FROM l IN Videogames.levels |
The following lines show the results of the query. Notice that the query specified the desired alias:
|
1 2 3 4 5 |
[ { "totalNumberOfLevels": 6 } ] |
Now you want to calculate the average tower power for the levels defined in the video games. The towerPower property is not defined for all the levels and it is only available for the levels of the game whose id is equal to 1. Whenever you use the AVG aggregate function to calculate an average for an expression, only the documents that have the property will be a part of the average calculation. Hence, the levels that don’t have the towerPower property won’t generate an impact on the average.
The following query uses the AVG aggregate function combined with the IN keyword to iterate all the levels of the games that have the towerPower property and compute its average value. The code file for the sample is included in the sql_queries/videogame_1_20.sql file:
|
1 2 |
SELECT AVG(l.towerPower) AS towerPowerAverage FROM l IN Videogames.levels |
The following lines show the results of the query. Notice that the query specified the desired alias:
|
1 2 3 4 5 |
[ { "towerPowerAverage": 56.666666666666664 } ] |
If you found this article interesting, you can explore Guide to NoSQL with Azure Cosmos DB to create scalable applications by taking advantage of NoSQL document databases on the cloud with .NET Core. Guide to NoSQL with Azure Cosmos DB will help build an application that works with a Cosmos DB NoSQL document database with C#, the .NET Core SDK, LINQ, and JSON.
Cheers!