374 reads

Velo How-To: API Aggregations

by Velo by WixJune 12th, 2021

Too Long; Didn't Read

Using the aggregation functionality of the Data API you can perform certain calculations on your collection data. You can also add filtering and sorting to your aggregations to retrieve meaningful summaries. 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. To work with aggregations, you will need to import the wix-data-API to use the functions described below.

featured image - Velo How-To: API Aggregations

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.

Note: Aggregations can only be used on collections you have created. They cannot be used on Wix App Collections.

Note: Before reading this article, you should be familiar with using the Data API. To learn more, see Working with the Data API.

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:

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

Importing wix-data

To work with aggregations, you will need to import

wix-data

import wixData from 'wix-data';

Running Aggregations

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

To run an aggregation:

Create an aggregation using the
```
aggregate()
```
function.
Refine the aggregation using the functions described below.
Run the aggregation using the
```
run()
```
function.
Handle the aggregation results.

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

wixData.aggregate("PopulationData")
  .max("population")
  .run()
  .then( (results) => {
    let populationMax = results.items[0].populationMax;
  } );

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.

import wixData from 'wix-data';

// ...

const filter = wixData.filter().eq("year", 2010);
const having = wixData.filter().gt("maxPopulation", 1000000);

wixData.aggregate("PopulationData")
  .filter(filter)
  .group("state")
  .max("population", "maxPopulation")
  .having(having)
  .descending("maxPopulation")
  .run()
  .then( (results) => {
    console.log(results.items);
    console.log(results.length);
    console.log(results.hasNext());
  } )
  .catch( (error) => {
    console.log(error.message);
    console.log(error.code);
  } );

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

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:

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

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

wixData.aggregate("PopulationData")
  .max("population")
  .run()
  .then( (results) => {
    let items = results.items;
  } );

/* items is:
 * [{"_id": "0", "populationMax": 8192000}]
 */

group(), max()

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

wixData.aggregate("PopulationData")
  .group("state")
  .max("population")
  .run()
  .then( (results) => {
    let items = results.items;
  } );

/* items is:
 * [
 *   {"_id": "FL", "populationMax": 401000},
 *   {"_id": "CA", "populationMax": 3796000},
 *   {"_id": "NY", "populationMax": 8192000}
 * ]
 */

group(), count()

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

wixData.aggregate("PopulationData")
  .group("state")
  .count()
  .run()
  .then( (results) => {
    let items = results.items;
  } );

/* items is:
 * [
 *   {"_id":"FL","count":4},
 *   {"_id":"CA","count":6},
 *   {"_id":"NY","count":4}
 * ]
 */

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.

wixData.aggregate("PopulationData")
  .group("state", "year")
  .max("population")
  .run()
  .then( (results) => {
    let items = results.items;
  } );

/* items is:
 * [
 *   {
 *     "_id": {"state": "NY", "year": 2000},
 *     "populationMax": 8015000,
 *     "state": "NY",
 *     "year": 2000
 *   },{
 *     "_id": {"state": "FL", "year": 2000},
 *     "populationMax": 362000,
 *     "state": "FL",
 *     "year": 2000
 *   },{
 *     "_id": {"state": "CA", "year": 2000},
 *     "populationMax": 3703000,
 *     "state": "CA",
 *     "year": 2000
 *   },{
 *     "_id": {"state": "FL", "year": 2010},
 *     "populationMax": 401000,
 *     "state": "FL",
 *     "year": 2010
 *   },{
 *     "_id": {"state": "CA", "year": 2010},
 *     "populationMax": 3796000,
 *     "state": "CA",
 *     "year": 2010
 *   },{
 *     "_id":{"state": "NY", "year": 2010},
 *     "populationMax": 8192000,
 *     "state": "NY",
 *     "year": 2010
 *   }
 * ]
 */

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

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

wixData.aggregate("PopulationData")
  .group("state", "year")
  .count()
  .run()
  .then( (results) => {
    let items = results.items;
  } );

/* items is:
 * [
 *   {
 *     "_id": {"state": "NY", "year": 2000},
 *     "count": 2,
 *     "state": "NY",
 *     "year": 2000
 *   },{
 *     "_id": {"state": "FL", "year": 2000},
 *     "count": 2,
 *     "state": "FL",
 *     "year": 2000
 *   },{
 *     "_id": {"state": "CA", "year": 2000},
 *     "count": 3,
 *     "state": "CA",
 *     "year": 2000
 *   },{
 *     "_id": {"state": "FL", "year": 2010},
 *     "count": 2,
 *     "state": "FL",
 *     "year": 2010
 *   },{
 *     "_id": {"state": "CA", "year": 2010},
 *     "count": 3,
 *     "state": "CA",
 *     "year": 2010
 *   },{
 *     "_id": {"state": "NY", "year": 2010},
 *     "count": 2,
 *     "state": "NY",
 *     "year": 2010
 *   }
 * ]
 */

filter(), max()

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

let filter = wixData.filter().eq("year", 2000);

wixData.aggregate("PopulationData")
  .filter(filter)
  .max("population")
  .run()
  .then( (results) => {
    let items = results.items;
  } );

// items is: [{"_id": "0", "populationMax": 8015000}]

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.

wixData.aggregate("PopulationData")
  .group("state")
  .max("population")
  .ascending("populationMax")
  .run()
  .then( (results) => {
    let items = results.items;
  } );

/* items is:
 * [
 *   {"_id": "FL", "populationMax": 401000},
 *   {"_id": "CA", "populationMax": 3796000},
 *   {"_id": "NY", "populationMax": 8192000}
 * ]
 */

Previously published at https://support.wix.com/en/article/velo-working-with-aggregations-in-the-data-api