Using the aggregation functionality of the 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. Data API Aggregations can only be used on collections you have created. They cannot be used on . Note: Wix App Collections Before reading this article, you should be familiar with using the Data API. To learn more, see . Note: 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. the .csv file into the collection. Import PopulationData 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 wixData ; import 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 function. aggregate() Refine the aggregation using the functions described below. Run the aggregation using the function. run() Handle the aggregation results. For example, here is a simple aggregation that finds the largest population value in the collection. PopulationData wixData.aggregate( )
  .max( )
  .run()
  .then( { populationMax = results.items[ ].populationMax;
  } ); "PopulationData" "population" ( ) => results let 0 You can also use the following functions with an aggregation to modify the results you receive: - Sets the number of items or groups to skip before returning aggregation results. skip() - Limits the number of items or groups the aggregation returns. limit() Aggregation Results To handle aggregation results, use the following properties and functions on the object returned in the function's Promise: aggregate() - 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. items - The number of items or groups in the aggregate results. length - 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. hasNext() - Retrieves the next page of aggregate results. next() 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. wixData ; filter = wixData.filter().eq( , ); having = wixData.filter().gt( , );

wixData.aggregate( )
  .filter(filter)
  .group( )
  .max( , )
  .having(having)
  .descending( )
  .run()
  .then( { .log(results.items); .log(results.length); .log(results.hasNext());
  } )
  .catch( { .log(error.message); .log(error.code);
  } ); import from 'wix-data' // ... const "year" 2010 const "maxPopulation" 1000000 "PopulationData" "state" "population" "maxPopulation" "maxPopulation" ( ) => results console console console ( ) => error console console filter() Use the function to narrow down which items are included in an aggregation. filter() For example, on line 9 of the aggregation above, the filter is used to filter out items where the is not . year 2010 The aggregate function takes a object created using the function (line 5 above). Use any of the filtering functions to build your object. filter() WixDataFilter wix-data.filter() WixDataFilter WixDataFilter group() Use the function to group retrieved items together and then optionally calculate aggregated values and further filter the groups. group() 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 function is used to get the largest population value from each of the state groups. max() You can perform the following aggregated calculations on groups: min() max() avg() sum() count() The function can also be used to create groups based on multiple fields. group() having() Use the function to narrow down which groups are included in an aggregation. The function differs from filter in that it is applied after the groupings are made. So filters out from the collection that you don't want considered at all while filters out that don't match the given criteria. having() having() filter() items having() groups For example, on line 12 of the aggregation above, the having is used to filter out groups where the is less or equal to . maxPopulation 1000000 The function takes a object created using the function (line 6 above). Use any of the filtering functions to build your object. having() WixDataFilter wix-data.filter() WixDataFilter WixDataFilter Sorting Use the and functions to sort the aggregation's resulting items or groups. ascending() decending() For example, on line 13 of the aggregation above, the results are sorted in descending order based on the aggregated values. maxPopulation 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 field, which is not a field in the collection, but a field created by the aggregation. maxPopulation max() 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 " " because we are calling the function and passing it the " " field key. populationMax max() population wixData.aggregate( )
  .max( )
  .run()
  .then( { items = results.items;
  } ); "PopulationData" "population" ( ) => results let /* items is:
 * [{"_id": "0", "populationMax": 8192000}]
 */ group(), max() This example finds the largest population value in each state across all years. wixData.aggregate( )
  .group( )
  .max( )
  .run()
  .then( { items = results.items;
  } ); "PopulationData" "state" "population" ( ) => results let /* 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( )
  .group( )
  .count()
  .run()
  .then( { items = results.items;
  } ); "PopulationData" "state" ( ) => results let /* 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( )
  .group( , )
  .max( )
  .run()
  .then( { items = results.items;
  } ); "PopulationData" "state" "year" "population" ( ) => results let /* 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( )
  .group( , )
  .count()
  .run()
  .then( { items = results.items;
  } ); "PopulationData" "state" "year" ( ) => results let /* 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. filter = wixData.filter().eq( , );

wixData.aggregate( )
  .filter(filter)
  .max( )
  .run()
  .then( { items = results.items;
  } ); let "year" 2000 "PopulationData" "population" ( ) => results let // 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( )
  .group( )
  .max( )
  .ascending( )
  .run()
  .then( { items = results.items;
  } ); "PopulationData" "state" "population" "populationMax" ( ) => results let /* 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

How To Use the Paid Plans API for Pricing Plan Ordering

Velo How-To: API Aggregations

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

5 Hot Velo Practices to Test and Debug Your Website Code

10 Cool CI/CD Tools For Your Project

27 Stories To Learn About Ssl

20 Essential Backend Tools For Developers

3 Most Common Ways to Connect your Node and React Applications

5 Hot Velo Practices to Test and Debug Your Website Code

10 Cool CI/CD Tools For Your Project

27 Stories To Learn About Ssl

20 Essential Backend Tools For Developers

3 Most Common Ways to Connect your Node and React Applications

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps