Working with aggregate functions
Cosmos DB SQL provides support for aggregations in the SELECT clause. For example, the following query uses 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 the sample is included in the learning_cosmos_db_03_01 folder in the sql_queries/videogame_1_17.sql file:
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": 6
}
] Note
Whenever we use an expression in the SELECT clause that is not a property name and we 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...