Aggregation functions aggregation-functions
Aggregation functions perform calculations on a set of values and return a single summarized result. These functions enable you to analyze data within your journey expressions by calculating averages, finding minimum and maximum values, counting elements, and summing numeric values.
Use aggregation functions when you need to:
- Calculate statistical values from lists or arrays (avg, sum, min, max)
- Count elements in collections (count, countOnlyNull, countWithNull), with options to include or exclude null values
- Determine unique values within data sets (distinctCount, distinctCountWithNull)
- Make data-driven decisions based on computed metrics
Aggregation functions automatically handle null values according to their specific behavior, making it easier to work with real-world data that may contain missing or undefined values.
avg avg
Returns the average value among a set of expressions, given either as a list or two expressions. Null values are ignored.
avg(<parameter>)Supported types:
- listInteger
- listDecimal
- decimal
- integer
avg(<listInteger>)
avg(<listDecimal>)
avg(<decimal>,<decimal>)
avg(<decimal>,<integer>)
avg(<integer>,<decimal>)
avg(<integer>,<integer>)
Returns a decimal.
avg(@event{BarBeacon.inventory},5)
avg([10,3,8])
Returns 7.0.
avg(10.2, 3)
Returns 6.6.
count count
Counts the elements of the list not taking into account the null values.
count(<listAny>)
count(<listObject>)
| table 0-row-3 1-row-3 | ||
|---|---|---|
| Parameter | Type | Description |
| listToProcess | listString, listBoolean, listInteger, listDecimal, listDuration, listDateTime, listDateTimeOnly, listDateOnly, or listObject | List to process. For listObject, it must be a field reference. A listObject cannot contain null object. |
count(<listAny>)
Returns an integer.
count([10,2,10,null])
Returns 3.
count(@event{my_event.productListItems})
Returns the number of objects in the given array of objects (listObject type). Remark: a listObject cannot contain null object
countOnlyNull countOnlyNull
Counts the number of null values in the list.
countOnlyNull(<listAny>)| table 0-row-2 1-row-2 | |
|---|---|
| Parameter | Type |
| listToProcess | listString, listBoolean, listInteger, listDecimal, listDuration, listDateTime, listDateTimeOnly, listDateOnly |
countOnlyNull(<listAny>)
Returns an integer.
countOnlyNull([10,2,10,null])
Returns 1.
Note: The parameter <listObject> is not supported in this function.
countWithNull countWithNull
Counts all the elements of the list including null values.
countWithNull(<listAny>)| table 0-row-2 1-row-2 | |
|---|---|
| Parameter | Type |
| listToProcess | listString, listBoolean, listInteger, listDecimal, listDuration, listDateTime, listDateTimeOnly, listDateOnly |
countWithNull(<listAny>)
Returns an integer.
countWithNull([10,2,10,null])
Returns 4.
Note: The parameter <listObject> is not supported in this function.
distinctCount distinctCount
Counts the number of different values ignoring the null values.
distinctCount(<listAny>)| table 0-row-3 1-row-3 2-row-3 | ||
|---|---|---|
| Parameter | Type | Description |
| listToProcess | listString, listBoolean, listInteger, listDecimal, listDuration, listDateTime, listDateTimeOnly, listDateOnly, or listObject | List to process. For listObject, it must be a field reference. |
| keyAttributeName | string | This parameter is optional and only for listObject. If the parameter is not provided, an object is considered as duplicated if all the attributes have the same values. Otherwise, an object is considered as duplicated if the given attribute has the same value. |
distinctCount(<listAny>)
Returns an integer.
distinctCount(<listObject>)
distinctCount(<listObject>,<string>)
Returns a list of objects.
distinctCount([10,2,10,null])
Returns 2.
distinctCount(@event{my_event.productListItems})
Returns the number of strictly distinct objects in the given array of objects (listObject type).
distinctCount(@event{my_event.productListItems}, "SKU")
Returns the number of objects which have a distinct “SKU” attribute value{}.
distinctCountWithNull distinctCountWithNull
Counts the number of different values including the null values.
distinctCountWithNull(<listAny>)| table 0-row-2 1-row-2 | |
|---|---|
| Parameter | Type |
| listToProcess | listString, listBoolean, listInteger, listDecimal, listDuration, listDateTime, listDateTimeOnly, listDateOnly |
distinctCountWithNull(<listAny>)
Returns an integer.
distinctCountWithNull([10,2,10,null])
Returns 3.
Note: The parameter <listObject> is not supported in this function.
max max
Returns the maximum value among a set of expressions, given either as a list or two expressions. Null values are ignored.
max(<parameter>)- listDuration
- listInteger
- listDecimal
- listDateTime
- listDateTimeOnly
- listDateOnly
- duration
- integer
- decimal
- dateTime
- dateTimeOnly
max(<listDuration>)
Returns a duration.
max(<listInteger>)
Returns a duration.
max(<listDateTimeOnly>)
Returns a datetime without considering time zone.
max(<listDateTime>)
Returns a datetime.
max(<listDateOnly>)
Returns a date.
max(<listDecimal>)
Returns a decimal.
max(<decimal>,<decimal>)
Returns a decimal.
max(<duration>,<duration>)
Returns a duration.
max(<dateTime>,<dateTime>)
Returns a datetime.
max(<dateTimeOnly>,<dateTimeOnly>)
Returns a datetime without considering time zone.
max(<integer>,<integer>)
Returns an integer.
max(@event{BarBeacon.inventory},5)
max([10,3,8])
Returns 10.
max([10,null,8])
Returns 10.
min min
Returns the minimum value among a set of expressions, given either as a list or two expressions. Null values are ignored.
min(<parameters>)- listDuration
- listInteger
- listDecimal
- listDateTime
- listDateTimeOnly
- listDateOnly
- duration
- integer
- decimal
- dateTime
- dateTimeOnly
min(<listDuration>)
Returns a duration.
min(<listInteger>)
Returns a duration.
min(<listDateTimeOnly>)
Returns a datetime without considering time zone.
min(<listDateTime>)
Returns a datetime.
min(<listDateOnly>)
Returns a date.
min(<listDecimal>)
Returns a decimal.
min(<decimal>,<decimal>)
Returns a decimal.
min(<duration>,<duration>)
Returns a duration.
min(<dateTime>,<dateTime>)
Returns a datetime.
min(<dateTimeOnly>,<dateTimeOnly>)
Returns a datetime without considering time zone.
min(<integer>,<integer>)
Returns an integer.
min(@event{BarBeacon.inventory},5)
min([10,3,8])
Returns 3.
min([10,null,8])
Returns 8.
sum sum
Returns the sum of the values of a set of expressions. Null values are ignored.
sum(<parameters>)- listInteger
- listDecimal
- duration
- integer
- decimal
sum(<listDecimal>)
Returns a decimal.
sum(<listInteger>)
Returns an integer.
sum(<integer>,<integer>)
Returns an integer.
sum(<decimal>,<decimal>)
Returns a decimal.
sum(@event{BarBeacon.inventory},5)
sum([10,3,8])
Returns 21.
sum([10.5,null,8.1])
Returns 18.6.
This section contains structured knowledge intended to support interpretation, retrieval, and question answering related to this topic.
For complete understanding, this information should be combined with the documentation on this page. Neither source is intended to stand alone; the page describes the feature, while this section provides additional context that helps disambiguate terminology, intent, applicability, and constraints.
- TL;DR: This page documents all aggregation functions available in AJO journey expressions, covering how to compute averages, sums, min/max values, counts, and distinct counts over lists and arrays.
Intents:
- Calculate the average of a list of numeric values using
avg - Sum numeric values in a list or from event fields using
sum - Find the minimum or maximum value in a list using
minormax - Count non-null, null-only, or all elements in a list using
count,countOnlyNull, orcountWithNull - Count distinct values in a list, with or without nulls, using
distinctCountordistinctCountWithNull - Filter unique objects in a listObject by a specific key attribute using
distinctCountwith a key parameter
Glossary:
- listObject: A list of complex objects (field references); cannot contain null objects (product-specific)
- listAny: A list of any supported scalar type (string, boolean, integer, decimal, duration, dateTime, dateTimeOnly, dateOnly) (product-specific)
- Null value: An absent or undefined element in a list; most aggregation functions ignore nulls unless the function explicitly handles them (e.g.,
countOnlyNull,countWithNull,distinctCountWithNull)
Guardrails:
countOnlyNull,countWithNull, anddistinctCountWithNulldo not support the<listObject>parameter typedistinctCounton alistObjectrequires the list to be a field reference, not an inline literalcounton alistObjectrequires the list to be a field reference; a listObject cannot contain null objects
Terminology:
- Canonical name: Aggregation functions — Acronym: none — variants: aggregate functions, collection functions
- Synonyms: “count” = “count non-null elements”; “countWithNull” = “count all elements including nulls”
- Do not confuse: “distinctCount” (ignores nulls) ≠ “distinctCountWithNull” (includes nulls as a distinct value)
FAQ:
- Q: Does
avginclude null values in its calculation? — No,avgignores null values automatically. - Q: What is the difference between
countandcountWithNull? —countexcludes null values from the total, whilecountWithNullcounts every element including nulls. - Q: Can I use
countOnlyNullon a listObject? — No,<listObject>is not supported bycountOnlyNull,countWithNull, ordistinctCountWithNull. - Q: How do I count distinct objects in an array based on a specific attribute? — Use
distinctCount(@event{...}, "attributeName")providing the key attribute name as the second parameter. - Q: What does
maxreturn when the list contains nulls? —maxignores null values and returns the maximum among the non-null elements.