Edit

Aggregate functions in Azure Cosmos DB

  • Article

APPLIES TO: NoSQL

Aggregate functions perform a calculation on a set of values in the SELECT clause and return a single value. For example, the following query returns the count of items within a container:

    SELECT COUNT(1)
    FROM c

Types of aggregate functions

The API for NoSQL supports the following aggregate functions. SUM and AVG operate on numeric values, and COUNT, MIN, and MAX work on numbers, strings, Booleans, and nulls.

Function Description
AVG Returns the average of the values in the expression.
COUNT Returns the number of items in the expression.
MAX Returns the maximum value in the expression.
MIN Returns the minimum value in the expression.
SUM Returns the sum of all the values in the expression.

You can also return only the scalar value of the aggregate by using the VALUE keyword. For example, the following query returns the count of values as a single number:

    SELECT VALUE COUNT(1)
    FROM Families f

The results are:

    [ 2 ]

You can also combine aggregations with filters. For example, the following query returns the count of items with the address state of WA.

    SELECT VALUE COUNT(1)
    FROM Families f
    WHERE f.address.state = "WA"

The results are:

    [ 1 ]

Remarks

These aggregate system functions will benefit from a range index. If you expect to do an AVG, COUNT, MAX, MIN, or SUM on a property, you should include the relevant path in the indexing policy.

Next steps