The OData service exposed by the API Server supports data aggregation and transformation, using the $apply keyword as defined in the OData specifications.

Aggregate

The keyword $apply supports the aggregate(…) transformation, which takes as arguments a list of comma-separated aggregation expressions. The API Server will return a result set corresponding to the aggregate method specified. This result set may have a different structure than the original resource.

Aggregation Expressions

Aggregation expressions follow the syntax: <ColumName> with <Method> as <Alias>, where:

• ColumnName: is the entity upon which the aggregation method is evaluated
• Method: is a supported aggregation methods
• Alias: is an OData-valid name to display the result of the aggregation in the result set

Supported Aggregation Methods

The API Server supports the following aggregation methods:

• countdistinct: Returns the number of separate, non-identical values on the column specified in the aggregated data set
• sum: Returns the sum of the values of the column specified over the aggregated data set
• max: Returns the maximum value of the column specified over the aggregated data set
• min: Returns the minimum value the column specified over the aggregated data set
• average: Returns the arithmetic mean of the column specified over the aggregated data set

Virtual Aggregation $count

In addition to the aggregation methods listed above, the API Server supports the $count virtual aggregation method. This method uses a different syntax than the main aggregation methods, it does not specify a column, only an alias: $count as <Alias>. The $count method returns the number of separate entities in an aggregated data set.

Example Aggregation requests:

Listed below are some example requests and result sets for the aggregation transformation:

• Find the number of available products:

GET Products$apply=aggregate($count as Total)

Response:

{
    "value": [
         {"@odata.id": null, "Total": 24}
    ]
}

• Return the number of Product categories, and the maximum product price:

GET Products?$apply=aggregate(Category with countdistinct as TotalCat, Price with max as MaxPrice)

Response:

{
    "value": [
        { "@odata.id": null, "TotalCat": 2, "MaxPrice": 1299.99}
    ]
}

Group By

The keyword $apply also supports groupby(…) transformations. This transformations takes one or two arguments. The first argument is a list of columns, enclosed within parentheses. The second argument, which is optional, is an aggregate transformation expression.

Grouping properties

The grouping properties argument of the groupby() transformation is a comma-separated list of columns, and is enclosed in parentheses. The transformation will divide the available entities into data sets in which ALL specified grouping columns are equal.

Aggregation Transformation

When used in combination with groupby(), the aggregation transformations will apply to each data set individually. The aggregate transformation argument follows the syntax of a standard aggregation.

Example Groupby Requests

Listed below are some example requests and result sets for the groupby() OData transformation.

Simple Group by:

These examples specify only the first argument of the groupby() transformation.

• Find all different values for Product categories:

GET Products?$apply=groupby((Category))

Response:

{
    "value": [
        { "@odata.id": null, "Category": "Bike" },
        { "@odata.id": null, "Category": "Shoes" }
    ]
}

• Find all combinations of category and color across existing Products:

GET Products?$apply=groupby((Category,Color))

Response:

{
    "value": [
        { "@odata.id": null, "Category": "Bike",  "Color": "Red"   },
        { "@odata.id": null, "Category": "Bike",  "Color": "Blue"  },
        { "@odata.id": null, "Category": "Shoes", "Color": "Black" },
        { "@odata.id": null, "Category": "Shoes", "Color": "Blue"  },
        { "@odata.id": null, "Category": "Shoes", "Color": "Red"   }
    ]
}

Group By and Aggregation

These examples specify both arguments of the groupby() transformation.

• Find the least expensive of each product category:

GET Products?$apply=groupby((Category), aggregate(Price with min as MinPrice))

Response:

{
    "value": [
        { "@odata.id": null, "Category": "Bike",  "MinPrice": 120},
        { "@odata.id": null, "Category": "Shoes", "MinPrice": 30}
    ]
}

• Find the number of available items for each combination of category and color, as well as the average price:

GET Products?$apply=groupby((Category,Color), aggregate($count as AvailProd, Price with average as AverageCost))

Response:

{
    "value": [
        { "@odata.id": null, "Category": "Bike",  "Color": "Red"  , "AvailProd": 12, "AverageCost": 514.34 },
        { "@odata.id": null, "Category": "Bike",  "Color": "Blue" , "AvailProd": 7,  "AverageCost": 520.17 },
        { "@odata.id": null, "Category": "Shoes", "Color": "Black", "AvailProd": 2, "AverageCost": 89.64   },
        { "@odata.id": null, "Category": "Shoes", "Color": "Blue" , "AvailProd": 9, "AverageCost": 79.84   },
        { "@odata.id": null, "Category": "Shoes", "Color": "Red"  , "AvailProd": 4, "AverageCost": 98.63   }
    ]
}

Data Aggregation and Filtering

The API Server supports filtering along with a data aggregation query. The filter can apply to the result of the aggregation, or conversely the aggregation can be applied to the results of the filter transformation.

Filtering Aggregated Data

The OData keywords $filter, $orderby, $top and $skip apply to the result of the aggregation. If $skip is specified, an $orderby value must be set as well.

GET Products?$apply=groupby((Color), aggregate($count as Total))&$filter=Total gt 0&$orderby=Total

Response:

{
    "value": [
        {"@odata.id": null, "Color": "Blue",  "Total": "1"},
        {"@odata.id": null, "Color": "Red",   "Total": "15"},
        {"@odata.id": null, "Color": "Green", "Total": "32"},
        {"@odata.id": null, "Color": "Black", "Total": "44"}
    ]
}

Aggregating on Filtered Data

To apply a filter on data prior to the aggregation, the $apply OData keyword supports the filter(…) transformation. The value of the filter is the same as OData filters using the $filter keyword. The aggregation will be applied on the result of the filtered data.

GET Products?$apply=filter(ProductCategoryId eq 10)/groupby((Color, ProductCategoryId), aggregate($count as Total))

Response:

{
    "value": [
        {"@odata.id": null, "ProductCategoryId": 10, "Color": "Red",   "Total": 8},
        {"@odata.id": null, "ProductCategoryId": 10, "Color": "Green", "Total": 12}
    ]
}