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.

Aggregation and grouping requests are processed on the server when the app is online and locally on the device when the app is in offline mode.

Using Aggregation and Grouping

To read aggregated or grouped data, make a new instance of the AggregateQuery class and then use its methods to specify the kind of aggregation or grouping you want.


var el = new Everlive('your-app-id');
var data = el.data('type-name');
var query = new Everlive.AggregateQuery();

query.groupBy(['group-field-name1'<, 'group-field-name2', ... 'group-field-nameN'>]);
query.<function1>('source-field-name1'<, 'result-field-name1'>);
query.<function2>('source-field-name2'<, 'result-field-name2'>);
...
query.<functionN>('source-field-nameN'<, 'result-field-nameN'>);

data.aggregate(query)
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

Where:

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

You can also chain aggregation methods.

query.groupBy(...).<function1>(...).<function2>(...);

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.

var el = new Everlive('your-app-id');
var data = el.data('Balance');
var query = new Everlive.AggregateQuery();

query.sum('Income', 'TotalIncome')
     .max('Income', 'BiggestIncome')
     .min('Income', 'SmallestIncome')
     .avg('Income', 'AverageIncome')
     .count('NumberOfDeals');

data.aggregate(query)
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

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 method 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.

var el = new Everlive('your-app-id');
var data = el.data('Balance');
var query = new Everlive.AggregateQuery();

query.groupBy(['Client']);
query.count('NumberOfDeals');
query.sum('Income', 'TotalIncome');


data.aggregate(query)
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

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 as an argument of the groupBy method but do not use any of the aggregation methods on the same instance.

var el = new Everlive('your-app-id');
var data = el.data('Balance');
var query = new Everlive.AggregateQuery();

query.groupBy(['Client']);

data.aggregate(query)
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

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.

var el = new Everlive('your-app-id');
var data = el.data('Balance');
var query = new Everlive.AggregateQuery();

query.groupBy(['Country', 'Client']);

data.aggregate(query)
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

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, call the where() method before calling the aggregation and grouping methods. Such queries are always first filtered, then aggregated.

var el = new Everlive('your-app-id');
var data = el.data('Balance');
var query = new Everlive.AggregateQuery();

query.where().gt('Income', 15000);

query.groupBy(['Client']);
query.count('NumberOfDeals');
query.sum('Income', 'TotalIncome');

data.aggregate(query)
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

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 as if using the RESTful API.

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
var everliveApp = new Everlive('your-app-id');

var expandExp = {
    "Products" : {
        "TargetTypeName": "Products",
        "Aggregate" : {
            "GroupBy" : ["ProductType"],
            "Aggregate": {
                "Count": {
                    "count": "ProductName"
                }
            }
        }
    }
};

var query = new Everlive.Query();
query.expand(expandExp);

var data = everliveApp.data('Balance');
data.get(query)
    .then(function(data){
        console.log(JSON.stringify(data));
    },
    function(error){
        console.log(JSON.stringify(error));
    });

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 when working online and 100,000 items when working offline. 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.