Velo: Working with Aggregations in the Data API

Using the aggregation functionality of the Data API you can perform certain calculations on your collection data, as whole or on groups of items that you define, to retrieve meaningful summaries. You can also add filtering and sorting to your aggregations to retrieve exactly what you need. 

Sample Data

For demonstration purposes we use the following sample data. The data represents population statistics for cities collected over multiple years. In our examples, we assume the data is contained in a collection named PopulationData. 

If you want to follow along with the examples in this article, you can:

1. Create a collection on your site named PopulationData.
2. Save the above data in a .csv file.
3. Import the .csv file into the PopulationData collection. 
4. Copy and paste the code snippets found below into the code editor on one of your sites pages.

Importing wix-data

To work with aggregations, you will need to import wix-data.

Running Aggregations

Running an aggregation is similar to running a query, but with some important differences. 

To run an aggregation:

1. Create an aggregation using the aggregate() function.
2. Refine the aggregation using the functions described below.
3. Run the aggregation using the run() function.
4. Handle the aggregation results.

For example, here is a simple aggregation that finds the largest population value in the PopulationData collection.

You can also use the following functions with an aggregation to modify the results you receive:

• skip() - Sets the number of items or groups to skip before returning aggregation results.
• limit() - Limits the number of items or groups the aggregation returns.

Aggregation Results

To handle aggregation results, use the following properties and functions on the object returned in the aggregate() function's Promise:

• items - An array of the aggregated items or groups. Each value is contained in an object that is an element of the array. The structure of the objects depends on which aggregations have been run.
• length - The number of items or groups in the aggregate results.
• hasNext() - Indicates if the aggregation has more results. Aggregation results are paged. So if your aggregation returns more results than the page size, you will have multiple pages of results.
• next() - Retrieves the next page of aggregate results.

Aggregations Structure

An aggregation is built with the following basic structure, where each part is optional. 

The parts of an aggregation are explained below using the following example aggregation, which finds the cities whose population in 2010 was the largest in their respective states and the population was over 1,000,000. 

filter()

Use the filter() function to narrow down which items are included in an aggregation.

For example, on line 9 of the aggregation above, the filter is used to filter out items where the year is not 2010.

The aggregate filter() function takes a WixDataFilter object created using the wix-data.filter() function (line 5 above). Use any of the WixDataFilter filtering functions to build your WixDataFilter object.

group()

Use the group() function to group retrieved items together and then optionally calculate aggregated values and further filter the groups.

Grouping is a powerful tool that allows you to aggregate data in groups instead of across a whole collection or part of a collection. When grouping is employed, aggregations are performed on the unique combinations that define each group.

For example, without grouping you can find the city with the largest population in a collection or you can find the city with the largest population in a specific state. However, with grouping you can find the city with the largest population in each state.

For example, on line 10 of the aggregation above, grouping is used to group together all cities in the same state. Then, on line 11, the max() function is used to get the largest population value from each of the state groups.

You can perform the following aggregated calculations on groups:

• min()
• max()
• avg()
• sum()
• count()

The group() function can also be used to create groups based on multiple fields.

having()

Use the having() function to narrow down which groups are included in an aggregation. The having() function differs from filter in that it is applied after the groupings are made. So filter() filters out items from the collection that you don't want considered at all while having() filters out groups that don't match the given criteria.

For example, on line 12 of the aggregation above, the having is used to filter out groups where the maxPopulation is less or equal to 1000000.

The having() function takes a WixDataFilter object created using the wix-data.filter() function (line 6 above). Use any of the WixDataFilter filtering functions to build your WixDataFilter object.

Sorting

Use the ascending() and decending() functions to sort the aggregation's resulting items or groups.

For example, on line 13 of the aggregation above, the results are sorted in descending order based on the aggregated maxPopulation values.

You can sort based on actual fields from your collection or virtual fields that are created as part of the grouping and aggregation process. For example, the sort on line 13 above is performed on the maxPopulation field, which is not a field in the collection, but a field created by the max() aggregation.

Examples

Here we present a number of common scenarios that demonstrate how to use aggregations in various ways.

max()

This example finds the largest population value of all the cities across all years. Notice that the key in the results is named "populationMax" because we are calling the max() function and passing it the "population" field ID.

group(), max()

This example finds the largest population value in each state across all years.

group(), count()

This example finds the number of items for each state across all years.

group(...multiple-fields), max()

You can also create groups based on multiple fields and then run aggregations on those groups. When grouping by multiple fields, each group is defined by a unique combination of all the fields in the group.

For example, here is an aggregation that finds the largest population in each state for each year with population data.

group(...multiple-fields), count()

This example finds the number of items for each state per year.

filter(), max()

This example uses a filter to find the city with the largest population in the year 2000.

group(), max(), ascending()

This example uses a sort to find the largest population in each state across all years and sorts them from least to greatest.