Progress will discontinue Telerik Platform on May 10th, 2018. Learn more

Data Aggregation and Grouping

Data aggregation and grouping allow you to query for data more efficiently, offloading the processing to the server and decreasing bandwidth usage for your app. For example you could directly request the sum of a number of fields instead of first reading them and then summing them locally.

Using Aggregation and Grouping

To read aggregated or grouped data, make a GET request to the _aggregate HTTP endpoint, passing a JSON object with the aggregate/group expression as a header or a query parameter:

  • Using the X-Everlive-Aggregate header:

    GET https://api.everlive.com/v1/<your-app-id>/<type-name>/_aggregate
    Header X-Everlive-Aggregate: <aggregate expression>
    
  • Using the aggregate query parameter:

    GET https://api.everlive.com/v1/<your-app-id>/<type-name>/_aggregate?aggregate=<aggregate expression>
    

The aggregate expression can contain these two optional clauses:

  • GroupBy, which is an array that can include one or more fields to group by.
  • Aggregate, which is an object that can contain one or more objects specifying what aggregation functions are used on what fields.
{
    "GroupBy": ["group-field-name1", "group-field-name2", ... "group-field-nameN"],
    "Aggregate": {
        "result-field-name1": {
            "function1": "source-field-name1"
        },
        "result-field-name2": {
            "function2": "source-field-name2"
        },
        ...
        "result-field-nameN": {
            "functionN": "source-field-nameN"
        }
    }
}

Where:

  • group-field-nameN is a field name by which you want to group.
  • functionN is the name of the aggregation function.
  • result-field-nameN is the name that you want to give to the aggregated field
  • source-field-nameN is the name of the field that you are aggregating

Aggregation Functions

The aggregate expression can contain any combination and number of the following aggregation functions:

Function Description
sum Returns the sum of all values in a content type field. The field type must be Number.
max Returns the maximum value found in a content type field. The field type must be Number or Date and Time.
min Returns the minimum value found in a content type field. The field type must be Number or Date and Time.
avg Returns the average of all values in a content type field. The field type must be Number.
count Returns the total count of records in a content type. If used in conjunction with grouping, it returns the record count for each group. In contrast to the other aggregation functions, you do not pass a source field.

The following example shows how to use all aggregation functions in a single request.

GET https://api.everlive.com/v1/<your-app-id>/Balance/_aggregate
Header X-Everlive-Aggregate
{
    "Aggregate": {
        "TotalIncome": {
            "sum": "Income"
        },
        "BiggestIncome": {
            "max": "Income"
        },
        "SmallestIncome": {
            "min": "Income"
        },
        "AverageIncome": {
            "avg": "Income"
        },
        "NumberOfDeals": {
            "count": null
        }
    }
}

Using the sample data, the example would return the following:

{
    "Result": [
        {
            "TotalIncome": 166000,
            "BiggestIncome": 50000,
            "SmallestIncome": 5000,
            "AverageIncome": 16600,
            "NumberOfDeals": 10
        }
    ]
}

Grouping

You can group aggregated data by a specified field or even multiple fields. To do this, use the GroupBy option in your aggregation query to specify the group by fields.

If you want to count grouped data, use the count aggregation function; the generic count query is not supported on aggregated/grouped data.

GET https://api.everlive.com/v1/<your-app-id>/Balance/_aggregate
Header X-Everlive-Aggregate
{
    "GroupBy": ["Client"],
    "Aggregate": {
        "NumberOfDeals": {
            "count": null
        },
        "TotalIncome": {
            "sum": "Income"
        }
    }
}

Using the sample data, the example would return the following:

{
    "Result": [
       {
          "Client": "The Nicest Pet Shop",
          "NumberOfDeals": 5,
          "TotalIncome": 66000
       },
       {
          "Client": "Cute Animals Kingdom",
          "NumberOfDeals": 3,
          "TotalIncome": 45000
       },
       {
          "Client": "Friendly Veterinary Shop",
          "NumberOfDeals": 2,
          "TotalIncome": 55000
       }
    ]
}

Requesting Distinct Values

To get the unique values of a field, specify it in the GroupBy option but do not make any Aggregate specifications.

GET https://api.everlive.com/v1/<your-app-id>/Balance/_aggregate
Header X-Everlive-Aggregate
{
    "GroupBy": ["Client"]
}

Using the sample data, the example would return the following:

{
    "Result": [
       {
          "Client": "The Nicest Pet Shop"
       },
       {
          "Client": "Cute Animals Kingdom"
       },
       {
          "Client": "Friendly Veterinary Shop"
       }
    ]
}

In case you specify multiple grouping fields, the result contains the unique combinations of the fields' values.

GET https://api.everlive.com/v1/<your-app-id>/Balance/_aggregate
Header X-Everlive-Aggregate
{
    "GroupBy": ["Country", "Client"]
}

Using the sample data, the example would return the following:

{
    "Result": [
       {
          "Country": "USA",
          "Client": "The Nicest Pet Shop"
       },
       {
          "Country": "USA",
          "Client": "Cute Animals Kingdom"
       },
       {
          "Country": "Canada",
          "Client": "Friendly Veterinary Shop"
       }
    ]
}

Aggregating Filtered Data

You can apply aggregation functions to data that has been first reduced using a filtering expression. To to this, include the X-Everlive-Filter and X-Everlive-Aggregate headers in a single query. Such queries are always first filtered, then aggregated.

GET https://api.everlive.com/v1/<your-app-id>/Balance/_aggregate
Header X-Everlive-Filter
{
    "Income": { "$gt" : 15000 }
}
Header X-Everlive-Aggregate
{
    "GroupBy": ["Client"],
    "Aggregate": {
        "NumberOfDeals": {
            "count": null
        },
        "TotalIncome": {
            "sum": "Income"
        }
    }
}

Using the sample data, the example would return the following:

{
    "Result": [
       {
          "Client": "The Nicest Pet Shop",
          "NumberOfDeals": 2,
          "TotalIncome": 38000
       },
       {
          "Client": "Cute Animals Kingdom",
          "NumberOfDeals": 1,
          "TotalIncome": 25000
       },
       {
          "Client": "Friendly Veterinary Shop",
          "NumberOfDeals": 1,
          "TotalIncome": 50000
       }
    ]
}

Aggregating Expanded Data

You can apply aggregation or grouping functions to expanded data that has been returned as a response to a relation expansion query. As a result, you receive the aggregated data instead of the expanded data.

To aggregate or group expanded data, add the Aggregate option to your expand expression and use it to specify a JSON aggregate expression.

Assume the sample Balance table features a Products field containing IDs of products stored in another content type called Products. You could expand the IDs to full product descriptions before aggregating them:

ID ProductName ProductType
1 Dog Biscuits Nutrition
2 Dog Chow Nutrition
3 Dry Cat Food Nutrition
4 Cat Pouch Nutrition
5 Tennis Ball Pet Toys
6 Rubber Bone Pet Toys
7 Cat Teaser Pet Toys
GET https://api.everlive.com/v1/<your-app-id>/Balance
Header X-Everlive-Expand
{
    "Products" : {
        "TargetTypeName": "Products",
        "Aggregate" : {
            "GroupBy" : ["ProductType"],
            "Aggregate": {
                "Count": {
                    "count": "ProductName"
                }
            }
        }
    }
}

Using the sample data, the example would return the following:

{
    "Count": 10,
    "Result": [
        {
            "Products": [
                {
                    "Count": 1,
                    "ProductType": "Pet Toys"
                }
            ],
            "Client": "The Nicest Pet Shop",
            "Country": "USA",
            "Income": 12000
        },
        {
            "Products": [],
            "Client": "The Nicest Pet Shop",
            "Country": "USA",
            "Income": 10000
        },
        {
            "Products": [
                {
                    "Count": 2,
                    "ProductType": "Pet Toys"
                },
                {
                    "Count": 2,
                    "ProductType": "Nutrition"
                }
            ],
            "Client": "Cute Animals Kingdom",
            "Country": "USA",
            "Income": 7000
        },
        {
            "Products": [
                {
                    "Count": 2,
                    "ProductType": "Nutrition"
                }
            ],
            "Client": "Friendly Veterinary Shop",
            "Country": "Canada",
            "Income": 5000
        },
        {
            "Products": [
                {
                    "Count": 1,
                    "ProductType": "Pet Toys"
                },
                {
                    "Count": 1,
                    "ProductType": "Nutrition"
                }
            ],
            "Client": "Cute Animals Kingdom",
            "Country": "USA",
            "Income": 13000
        },
        {
            "Products": [],
            "Client": "The Nicest Pet Shop",
            "Country": "USA",
            "Income": 20000
        },
        {
            "Products": [
                {
                    "Count": 1,
                    "ProductType": "Pet Toys"
                },
                {
                    "Count": 1,
                    "ProductType": "Nutrition"
                }
            ],
            "Client": "The Nicest Pet Shop",
            "Country": "USA",
            "Income": 18000
        },
        {
            "Products": [
                {
                    "Count": 1,
                    "ProductType": "Pet Toys"
                },
                {
                    "Count": 1,
                    "ProductType": "Nutrition"
                }
            ],
            "Client": "Cute Animals Kingdom",
            "Country": "USA",
            "Income": 25000
        },
        {
            "Products": [
                {
                    "Count": 1,
                    "ProductType": "Pet Toys"
                },
                {
                    "Count": 2,
                    "ProductType": "Nutrition"
                }
            ],
            "Client": "The Nicest Pet Shop",
            "Country": "USA",
            "Income": 6000
        },
        {
            "Products": [
                {
                    "Count": 3,
                    "ProductType": "Pet Toys"
                },
                {
                    "Count": 1,
                    "ProductType": "Nutrition"
                }
            ],
            "Client": "Friendly Veterinary Shop",
            "Country": "Canada",
            "Income": 50000
        }
    ]
}

Cloud Code Events

You can hook to a couple of Cloud Code events to make additional processing when aggregating or grouping data: beforeAggregate and afterAggregate. Find more information about using Cloud Code events, see Data Events for Cloud Code.

Sample Data Structure

The examples in this article are based on the following imaginary content type called Balance:

Client Country Income Products
The Nicest Pet Shop USA 12000 7
The Nicest Pet Shop USA 10000
Cute Animals Kingdom USA 7000 5, 4, 1, 7
Friendly Veterinary Shop Canada 5000 2, 3
Cute Animals Kingdom USA 13000 5, 1
The Nicest Pet Shop USA 20000
Friendly Veterinary Shop Canada 50000 7, 6, 5, 4
The Nicest Pet Shop USA 18000 4, 5
Cute Animals Kingdom USA 25000 1, 6
The Nicest Pet Shop USA 6000 1, 2, 6

Limitations

  • Using additional query options such as filtering, paging, sorting, etc. is not supported on aggregated/grouped data.
  • Aggregation and grouping on content types from Data Connectors is not supported.
  • Aggregation and grouping on Files is not supported.
  • Kendo UI DataSource:
    • serverAggregates: true is supported; ensure that serverFiltering is also set to true.
    • serverGrouping is not supported.
  • Aggregation and grouping are allowed on no more than 500,000 items. To ensure that you stay within that number, always filter down your data to a more representative data set before running any aggregation functions or grouping.

See Also


Start a free trial Request a demo
Contact us: +1-888-365-2779
sales@telerik.com
Copyright © 2016-2017, Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.