Version 23.0.9145

• Aggregate
  • Aggregation Expressions
    • Supported Aggregation Methods
    • Virtual Aggregation $count
  • Example Aggregation Requests:
    • Find the Number of Available Products
    • Return the Number of Product Categories with the Maximum Product Price
• Group By
  • Grouping Properties
  • Aggregation Transformation
  • Simple Groupby Examples
    • Find all Different Values for Product Categories
    • Find all Combinations of Category and Color across Existing Products
  • Groupby and Aggregation Examples
    • Find the Least Expensive Item in each Product Category
    • Find the Number of Available Items for all Combinations of Category and Color, with the Average Price
• Data Aggregation and Filtering
  • Filtering Aggregated Data
  • Aggregating on Filtered Data

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

Aggregate

The $apply keyword supports the aggregate(...) transformation, which takes as arguments a list of comma-separated aggregation expressions. API Server returns a result set corresponding to the aggregate method specified. This result set might 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

API Server supports the following aggregation methods:

Virtual Aggregation $count

In addition to the aggregation methods listed above, 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:

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

Find the Number of Available Products

Request

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

Response

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

Return the Number of Product Categories with the Maximum Product Price

Request

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 $apply keyword also supports groupby(...) transformations. This transformation takes one or two arguments. The first argument is a list of columns, enclosed in 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 divides 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 apply to each data set individually. The aggregate transformation argument uses the same syntax as a standard aggregation.

Simple Groupby Examples

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

Find all Different Values for Product Categories

Request

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

Request

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"   }
    ]
}

Groupby and Aggregation Examples

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

Find the Least Expensive Item in each Product Category

Request

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 all Combinations of Category and Color, with the Average Price

Request

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

API Server supports filtering with a data aggregation query. The filter can apply to the result of the aggregation, or 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 you specify $skip, you must also set an $orderby value.

Request

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, you can use the $apply OData keyword to support the filter(...) transformation. The value of the filter is the same as OData filters that use the $filter keyword. The aggregation is applied on the result of the filtered data.

Request

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}
    ]
}