> Portal Navigation: > > - Append `.md` to any URL under `https://dev.wix.com/docs/` to get its markdown version. > - Pages are either content pages (article or reference text) or menu pages (a list of links to child pages). > - To get a menu page, truncate any URL to a parent path and append `.md` (e.g. `https://dev.wix.com/docs/sdk.md`, `https://dev.wix.com/docs/sdk/core-modules.md`). > - Top-level index of all portals: https://dev.wix.com/docs/llms.txt > - Full concatenated docs: https://dev.wix.com/docs/llms-full.txt # AggregateDataItems # Package: cms # Namespace: DataItemService # Method link: https://dev.wix.com/docs/api-reference/business-solutions/cms/data-items/aggregate-data-items.md ## Permission Scopes: Read Data Items: SCOPE.DC-DATA.READ ## Introduction Runs an aggregation on a data collection and returns the resulting list of items. An aggregation enables you to perform certain calculations on your collection data, or on groups of items that you define, to retrieve meaningful summaries. You can also add paging, filtering, and sorting preferences to your aggregation to retrieve exactly what you need. --- ## REST API ### Schema ``` Method: aggregateDataItems Description: Runs an aggregation on a data collection and returns the resulting list of items. An aggregation enables you to perform certain calculations on your collection data, or on groups of items that you define, to retrieve meaningful summaries. You can also add paging, filtering, and sorting preferences to your aggregation to retrieve exactly what you need. URL: https://www.wixapis.com/wix-data/v2/items/aggregate Method: POST # Note: If the parameter `a.b` is listed under required parameters, `b` is only required if `a` is also present. Required parameters: dataCollectionId Method parameters: param name: aggregation | type: Aggregation - name: groupingFields | type: array | description: Fields by which to group items for the aggregation. If empty, the aggregation is carried out on all items in the collection. - name: operations | type: array | description: Operations to carry out on the data in each grouping. - ONE-OF: - name: average | type: Average | description: Calculate the average value of a specified field for all items in the grouping. - name: itemFieldName | type: string | description: Name of the field for which to calculate the average value. - name: min | type: Min | description: Calculate the minimum value of a specified field for all items in the grouping. - name: itemFieldName | type: string | description: Name of the field for which to calculate the minimum value. - name: max | type: Max | description: Calculate the maximum value of a specified field for all items in the grouping. - name: itemFieldName | type: string | description: Name of the field for which to calculate the maximum value. - name: sum | type: Sum | description: Calculate the sum of values of a specified field for all items in the grouping. - name: itemFieldName | type: string | description: Name of the field for which to calculate the sum. - name: itemCount | type: Count | description: Calculate the number of items in the grouping. EMPTY-OBJECT {} - name: resultFieldName | type: string | description: Name of the field containing results of the operation. param name: appOptions | type: appOptions | description: Additional parameters specific to the [Wix app collection](https://support.wix.com/en/article/cms-formerly-content-manager-working-with-wix-app-collections) you are querying. When querying the Wix Stores [Products collection](https://dev.wix.com/docs/develop-websites/articles/wix-apps/wix-e-commerce-stores/wix-stores-products-collection-fields.md), pass the following optional parameters: - `includeHiddenProducts`: Whether to include hidden products in the response. Default: `false`. - `includeVariants`: Whether to include product variants in the query. Default: `false`. param name: consistentRead | type: consistentRead | description: Whether to retrieve data from the primary database instance. This decreases performance but ensures data retrieved is up to date even immediately after an update. Learn more about [Wix Data and eventual consistency](https://dev.wix.com/api/rest/wix-data/wix-data/eventual-consistency). Default: `false` param name: dataCollectionId | type: dataCollectionId | description: GUID of the collection on which to run the aggregation. | required: true param name: finalFilter | type: finalFilter | description: Filter applied to the processed data following the aggregation. See [API Query Language](https://dev.wix.com/api/rest/getting-started/api-query-language#getting-started_api-query-language_the-filter-section) for information on how to structure a filter object. **Note:** The values you provide for each filter field must adhere to that field's type. For example, when filtering by a field whose type is Date and Time, use an object in the following format: `"someDateAndTimeFieldKey": { "$date": "YYYY-MM-DDTHH:mm:ss.sssZ"}`. Learn more about [data types in Wix Data](https://dev.wix.com/docs/rest/business-solutions/cms/data-items/data-types-in-wix-data.md). param name: initialFilter | type: initialFilter | description: Filter applied to the collection's data prior to running the aggregation. See [API Query Language](https://dev.wix.com/api/rest/getting-started/api-query-language#getting-started_api-query-language_the-filter-section) for information on how to structure a filter object. **Note:** The values you provide for each filter field must adhere to that field's type. For example, when filtering by a field whose type is Date and Time, use an object in the following format: `"someDateAndTimeFieldKey": { "$date": "YYYY-MM-DDTHH:mm:ss.sssZ"}`. Learn more about [data types in Wix Data](https://dev.wix.com/docs/rest/business-solutions/cms/data-items/data-types-in-wix-data.md). param name: language | type: language | description: Language to run the aggregation on, in [IETF BCP 47 language tag](https://en.wikipedia.org/wiki/IETF_language_tag) format. If not provided, the aggregation runs on items in the default language. > **Note:** The site must have [Wix Multilingual](https://www.wix.com/app-market/wix-multilingual) installed, and translation for the specified language must be enabled for the collection. Learn more about [Using Wix Multilingual to Translate CMS Content](https://support.wix.com/en/article/wix-multilingual-translating-cms-collection-content). - ONE-OF: - name: paging | type: Paging | description: Paging options to limit and skip the number of items. - name: limit | type: integer | description: Number of items to load. - name: offset | type: integer | description: Number of items to skip in the current sort order. - name: cursorPaging | type: CursorPaging | description: Cursor token pointing to a page of results. Not used in the first request. Following requests use the cursor token and not `filter` or `sort`. - name: limit | type: integer | description: Number of items to load. - name: cursor | type: string | description: Pointer to the next or previous page in the list of results. You can get the relevant cursor token from the `pagingMetadata` object in the previous call's response. Not relevant for the first request. param name: publishPluginOptions | type: PublishPluginOptions - name: includeDraftItems | type: boolean | description: Whether to include draft items. When `true`, both published and draft items are affected. Default: `false`. param name: returnTotalCount | type: returnTotalCount | description: Whether to return the total count in the response for a query with offset paging. When `true`, the `pagingMetadata` object in the response contains a `total` field. Default: `false` param name: sort | type: array | description: Sort object in the following format: `[{"fieldName":"sortField1","order":"ASC"},{"fieldName":"sortField2","order":"DESC"}]` - name: fieldName | type: string | description: Name of the field to sort by. - name: order | type: SortOrder | description: Sort order. - enum: ASC, DESC Return type: AggregateDataItemsResponse - name: results | type: array | description: Aggregation results. - name: pagingMetadata | type: PagingMetadataV2 | description: Paging information. - name: count | type: integer | description: Number of items returned in the response. - name: offset | type: integer | description: Offset that was requested. - name: total | type: integer | description: Total number of items that match the query. Returned if offset paging is used, `returnTotalCount` is `true` in the request, and `tooManyToCount` is false. - name: tooManyToCount | type: boolean | description: Whether the server failed to calculate the `total` field. - name: cursors | type: Cursors | description: Cursors to navigate through the result pages using `next` and `prev`. Returned if cursor paging is used. - name: next | type: string | description: Cursor pointing to next page in the list of results. - name: prev | type: string | description: Cursor pointing to previous page in the list of results. ``` ### Examples ### Calculate the total population in every state of year 2022 and return only those where population is greater than 1 million. ```curl curl -X POST \ 'https://www.wixapis.com/wix-data/v2/items/aggregate' \ -H 'Content-Type: application/json' \ -H 'Authorization: ' \ -d '{ "dataCollectionId": "cities", "initialFilter": { "year": 2022 }, "aggregation": { "groupingFields": ["state"], "operations": [ { "resultFieldName": "totalPopulation", "sum": { "itemFieldName": "population" } } ] }, "finalFilter": { "totalPopulation": { "$gt": 1000000 } }, "sort": [ { "fieldName": "totalPopulation", "order": "ASC" } ] }' ``` ### Calculate the sum of population in every state of year 2022 ```curl curl -X POST \ 'https://www.wixapis.com/wix-data/v2/items/aggregate' \ -H 'Content-Type: application/json' \ -H 'Authorization: ' \ -d '{ "dataCollectionId": "cities", "initialFilter": { "year": 2022 }, "aggregation": { "groupingFields": ["state"], "operations": [ { "resultFieldName": "totalPopulation", "sum": { "itemFieldName": "population" } } ] } }' ``` --- ## JavaScript SDK ### Schema ``` Method: wixClientAdmin.cms.DataItemService.aggregateDataItems(dataCollectionId, pipeline, options) Description: Runs an aggregation on a data collection and returns the resulting list of items. An aggregation enables you to perform certain calculations on your collection data, or on groups of items that you define, to retrieve meaningful summaries. You can also add paging, filtering, and sorting preferences to your aggregation to retrieve exactly what you need. # Note: If the parameter `a.b` is listed under required parameters, `b` is only required if `a` is also present. Required parameters: dataCollectionId, pipeline Method parameters: param name: dataCollectionId | type: string | description: GUID of the collection on which to run the aggregation. | required: true param name: options | type: WixDataAggregateOptions none - name: returnTotalCount | type: boolean | description: Whether to return the total count in the response for a query with offset paging. When `true`, the `pagingMetadata` object in the response contains a `total` field. Default: `false` - name: consistentRead | type: boolean | description: Whether to retrieve data from the primary database instance. This decreases performance but ensures data retrieved is up to date even immediately after an update. Learn more about [Wix Data and eventual consistency](https://dev.wix.com/api/rest/wix-data/wix-data/eventual-consistency). Default: `false` - name: language | type: string | description: Language to run the aggregation on, in [IETF BCP 47 language tag](https://en.wikipedia.org/wiki/IETF_language_tag) format. If not provided, the aggregation runs on items in the default language. > **Note:** The site must have [Wix Multilingual](https://www.wix.com/app-market/wix-multilingual) installed, and translation for the specified language must be enabled for the collection. Learn more about [Using Wix Multilingual to Translate CMS Content](https://support.wix.com/en/article/wix-multilingual-translating-cms-collection-content). - name: appOptions | type: object | description: Additional parameters specific to the [Wix app collection](https://support.wix.com/en/article/cms-formerly-content-manager-working-with-wix-app-collections) you are querying. When querying the Wix Stores [Products collection](https://dev.wix.com/docs/develop-websites/articles/wix-apps/wix-e-commerce-stores/wix-stores-products-collection-fields.md), pass the following optional parameters: - `includeHiddenProducts`: Whether to include hidden products in the response. Default: `false`. - `includeVariants`: Whether to include product variants in the query. Default: `false`. - name: showDrafts | type: boolean | description: When `true`, operations include draft items. Read operations include draft items in their response, and write operations modify draft items. - name: suppressHooks | type: boolean | description: Prevents hooks from running for the operation. Can only be used in the [backend code of a Wix site](https://dev.wix.com/docs/develop-websites/articles/coding-with-velo/backend-code/about-the-site-backend.md). param name: pipeline | type: AggregationPipeline | required: true - ONE-OF: - required: true - name: paging | type: Paging | description: Paging options to limit and skip the number of results. Learn more about [sorting and paging](https://dev.wix.com/docs/rest/articles/get-started/sorting-and-paging.md). - name: limit | type: integer | description: Number of items to load. - name: offset | type: integer | description: Number of items to skip in the current sort order. - name: cursorPaging | type: CursorPaging | description: Cursor token referring to a page of results. Not used in the first request. Subsequent requests use the cursor token and not `filter` or `sort`. - name: limit | type: integer | description: Number of items to load. - name: cursor | type: string | description: Pointer to the next or previous page in the list of results. You can get the relevant cursor token from the `pagingMetadata` object in the previous call's response. Not relevant for the first request. - name: stages | type: array | description: Sequence of aggregation operations to apply to the data. Stages are processed in order, with each stage operating on the output of the previous stage. - ONE-OF: - name: group | type: Group | description: Groups aggregated items to new result items. - name: groupIds | type: array | description: Grouping criteria by which to organize items. Each `groupId` specifies a field by which to group items, and serves as the result item's unique `_id` property. Items with identical values for the specified `expression` are placed in the same group based on the specified `key`. If empty, all items are treated as a single group. - name: key | type: string | description: Name of the field to group items by. This becomes the field name in the result's `_id` property. - name: expression | type: Expression | description: Expression to determine the grouping value. Items whose expression resolves to the same value are grouped together in the result item's `_id` property. - ONE-OF: - name: fieldPath | type: string | description: Path to the field containing the value to resolve. Use dot notation to specify nested fields. For example, `user.name` or `product.price`. - name: text | type: string | description: Text value. - name: numeric | type: number | description: Number value. - name: add | type: AddOperation | description: Adds the specified expressions together. - name: expressions | type: array | description: Expressions to add together. All expressions must resolve to numbers. - name: sum | type: SumOperation | description: Calculates the total sum of multiple expressions. - name: expressions | type: array | description: Expressions to calculate the total sum of. All expressions must resolve to numbers. - name: subtract | type: SubtractOperation | description: Subtracts 1 expression from another. - name: firstExpression | type: Expression | description: Expression to subtract `secondExpression` from. Expression must resolve to a number. - name: secondExpression | type: Expression | description: Expression to subtract from `firstExpression`. Expression must resolve to a number. - name: multiply | type: MultiplyOperation | description: Multiplies multiple expressions together. - name: expressions | type: array | description: Expressions to multiply. All expressions must resolve to numbers. - name: divide | type: DivideOperation | description: Divides 1 expression by another. - name: firstExpression | type: Expression | description: Expression to divide by `secondExpression`. Expression must resolve to a number. - name: secondExpression | type: Expression | description: Expression to divide `firstExpression` by. Expression must resolve to a non-zero number. - name: abs | type: AbsOperation | description: Finds the absolute value of an expression. - name: expression | type: Expression | description: Expression to find the absolute value of. Expression must resolve to a number. - name: mod | type: ModOperation | description: Finds the remainder when dividing 1 expression by another. - name: firstExpression | type: Expression | description: Expression to divide by `secondExpression` to find the remainder. Expression must resolve to a number. - name: secondExpression | type: Expression | description: Expression to divide `firstExpression` by. Expression must resolve to a non-zero number. - name: floor | type: FloorOperation | description: Rounds an expression down to the nearest whole number. - name: expression | type: Expression | description: Expression to round down to the nearest whole number. Expression must resolve to a number. - name: ceil | type: CeilOperation | description: Rounds an expression up to the nearest whole number. - name: expression | type: Expression | description: Expression to round up to the nearest whole number. Expression must resolve to a number. - name: concat | type: ConcatOperation | description: Joins multiple expressions together to create a string. - name: expressions | type: array | description: Expressions to join together. All expressions must resolve to strings. - name: stringify | type: StringifyOperation | description: Converts an expression to a string. - name: expression | type: Expression | description: Expression to convert to a string. - name: toLower | type: ToLowerOperation | description: Converts an expression to lowercase. - name: expression | type: Expression | description: Expression to convert to lowercase. Expression must resolve to a string. - name: toUpper | type: ToUpperOperation | description: Converts an expression to uppercase. - name: expression | type: Expression | description: Expression to convert to uppercase. Expression must resolve to a string. - name: substring | type: SubstringOperation | description: Extracts a portion of a string expression. - name: expression | type: Expression | description: Expression to extract a substring from. Expression must resolve to a string. - name: startExpression | type: Expression | description: Starting position of the substring to extract, specified in zero-based indexing. Expression must resolve to a number. - name: lengthExpression | type: Expression | description: Number of characters to extract from the starting position. Expression must resolve to a number. - name: length | type: LengthOperation | description: Finds the length of a string expression. - name: expression | type: Expression | description: Expression to count the total number of characters of. Expression must resolve to a string. - name: accumulators | type: array | description: Accumulation operations to run on each group. Accumulators aggregate data across all items in the group and return the result in a new field. > **Note:** When using accumulators, the type of expression must be compatible with the operation type. For example, when using the `sum` accumulator, the specified expression must resolve to a number. - ONE-OF: - name: avg | type: Avg | description: Calculates the average value across all items in the group based on the specified expression. - name: expression | type: Expression | description: Expression to calculate the group's average value. Expression must resolve to a number. - name: min | type: Min | description: Finds the minimum value across all items in the group. - name: expression | type: Expression | description: Expression to find the group's minimum value. Expression must resolve to a comparable value, such as a number or string. - name: max | type: Max | description: Finds the maximum value across all items in the group. - name: expression | type: Expression | description: Expression to find the group's maximum value. Expression must resolve to a comparable value, such as a number or string. - name: sum | type: Sum | description: Calculates the sum of the specified expression across all items in the group. - name: expression | type: Expression | description: Expression to calculate the group's total sum. Expression must resolve to a number. - name: first | type: First | description: Finds the first item in the group. - name: expression | type: Expression | description: Expression to find the first item in the group. - name: last | type: Last | description: Finds the last item in the group. - name: expression | type: Expression | description: Expression to find the last item in the group. - name: push | type: Push | description: Collects values from all items in the group into an array. - name: expression | type: Expression | description: Expression to collect items into an array. - name: resultFieldName | type: string | description: Field key in the output item to store the result in. - name: filter | type: object | description: Aggregation filter. Only items that match the filter are processed. Learn more about [filters in API Query Language](https://dev.wix.com/docs/rest/articles/get-started/api-query-language.md#filters). > **Note:** The values you provide for each filter field must adhere to that field's data type. For example, when filtering by a field whose type is Date and Time, use an object in the following format: `"someDateAndTimeFieldKey": { "$date": "YYYY-MM-DDTHH:mm:ss.sssZ"}`. Learn more about [data types in Wix Data](https://dev.wix.com/docs/rest/business-solutions/cms/data-items/data-types-in-wix-data.md). - name: sort | type: Sorting | description: Sorting preferences. Learn more about [sorting and paging](https://dev.wix.com/docs/rest/articles/get-started/sorting-and-paging.md). - name: values | type: array | description: Fields to sort the results by. - name: fieldName | type: string | description: Name of the field to sort by. - name: order | type: SortOrder | description: Sort order. - enum: ASC, DESC - name: projection | type: Projection | description: Transforms items by including, excluding, or reshaping fields. - name: fields | type: array | description: Fields to include, exclude, or reshape. - ONE-OF: - name: include | type: Include | description: When specified, includes only the specified field in the result. All other fields are excluded. - name: fieldName | type: string | description: Key of the field to include in the result. Use dot notation to specify nested fields. For example, `user.name` or `product.price`. Learn more about [field keys](https://support.wix.com/en/article/cms-formerly-content-manager-about-your-collection-fields#field-id-velo-by-wix-only). - name: exclude | type: Exclude | description: When specified, excludes the specified field from the result. All other fields are included. - name: fieldName | type: string | description: Key of the field to exclude from the result. Use dot notation to specify nested fields. For example, `user.name` or `product.price`. Learn more about [field keys](https://support.wix.com/en/article/cms-formerly-content-manager-about-your-collection-fields#field-id-velo-by-wix-only). - name: reshape | type: Reshape | description: Reshapes a new field based on the specified expression. The field used by the expression is not included in the result. - name: resultFieldName | type: string | description: Key of the field to create in the result item. Learn more about [field keys](https://support.wix.com/en/article/cms-formerly-content-manager-about-your-collection-fields#field-id-velo-by-wix-only). - name: expression | type: Expression | description: Expression to resolve. The result of the expression becomes the value of the new field. - name: projectNested | type: ProjectNested | description: Creates nested fields by including, excluding, or reshaping fields. - name: resultFieldName | type: string | description: Key of the parent field for the nested fields. Learn more about [field keys](https://support.wix.com/en/article/cms-formerly-content-manager-about-your-collection-fields#field-id-velo-by-wix-only). - name: fields | type: array | description: Nested fields to include, exclude, or reshape. - name: objectToArray | type: ObjectToArray | description: Transforms an object into an array where each object property becomes a new array element containing the property's key and value, as well as all other properties from the original object. - name: objectFieldName | type: string | description: Key of the object field to convert to an array. Use dot notation to specify nested fields. For example, `user.name` or `product.price`. Learn more about [field keys](https://support.wix.com/en/article/cms-formerly-content-manager-about-your-collection-fields#field-id-velo-by-wix-only). - name: destinationFieldName | type: string | description: Key of the new array to create in the result item. Learn more about [field keys](https://support.wix.com/en/article/cms-formerly-content-manager-about-your-collection-fields#field-id-velo-by-wix-only). - name: unwindArray | type: UnwindArray | description: Creates a result item for each array element. Each result contains the full original item, with the array field replaced by an element whose key is the array name and whose value is the array element. - name: arrayFieldName | type: string | description: Key of the array field to unwind. Use dot notation to specify a nested array. For example, `user.products` or `product.colors`. Learn more about [field keys](https://support.wix.com/en/article/cms-formerly-content-manager-about-your-collection-fields#field-id-velo-by-wix-only). - name: skip | type: integer | description: Number of results to skip from the beginning of the result set. - name: limit | type: integer | description: Maximum number of results to return. Return type: PROMISE - name: results | type: array | description: Aggregation results. - name: pagingMetadata | type: PagingMetadataV2 | description: Paging information. - name: count | type: integer | description: Number of items returned in the response. - name: offset | type: integer | description: Offset that was requested. - name: total | type: integer | description: Total number of items that match the query. Returned if offset paging is used, `returnTotalCount` is `true` in the request, and `tooManyToCount` is false. - name: tooManyToCount | type: boolean | description: Whether the server failed to calculate the `total` field. - name: cursors | type: Cursors | description: Cursors to navigate through the result pages using `next` and `prev`. Returned if cursor paging is used. - name: next | type: string | description: Cursor pointing to next page in the list of results. - name: prev | type: string | description: Cursor pointing to previous page in the list of results. ``` ### Examples ### aggregate ```javascript import { items } from '@wix/data'; async function aggregate(dataCollectionId,pipeline,options) { const response = await items.aggregate(dataCollectionId,pipeline,options); }; ``` ### aggregate (with elevated permissions) ```javascript import { items } from '@wix/data'; import { auth } from '@wix/essentials'; async function myAggregateMethod(dataCollectionId,pipeline,options) { const elevatedAggregate = auth.elevate(items.aggregate); const response = await elevatedAggregate(dataCollectionId,pipeline,options); } ``` ### aggregate (self-hosted) Self-hosted SDK calls require you to [create a client](https://dev.wix.com/docs/sdk/articles/work-with-the-sdk/about-the-wix-client.md). ```javascript import { createClient } from '@wix/sdk'; import { items } from '@wix/data'; // Import the auth strategy for the relevant access type // Import the relevant host module if needed const myWixClient = createClient ({ modules: { items }, // Include the auth strategy and host as relevant }); async function aggregate(dataCollectionId,pipeline,options) { const response = await myWixClient.items.aggregate(dataCollectionId,pipeline,options); }; ``` ---