Aggregations

COLLECT for grouping, AGGREGATE for computing SUM, AVG, COUNT, MIN, MAX and statistical functions.

Group & Aggregate

Use the COLLECT clause to group documents and perform aggregations like counting, summing, or averaging.

Basic Grouping

Group results by one or more fields to find distinct values.

FOR doc IN users
  COLLECT city = doc.city
  RETURN { city }

Counting Groups

Count the number of items in each group using WITH COUNT INTO.

FOR doc IN users
  COLLECT city = doc.city
  WITH COUNT INTO count
  RETURN { city, count }

Aggregation Functions

Calculate aggregates using functions like SUM, AVG, MIN, MAX.

FOR s IN sales
  COLLECT product = s.product
  AGGREGATE
    total = SUM(s.amount),
    average = AVG(s.amount),
    min_amt = MIN(s.amount),
    max_amt = MAX(s.amount)
  RETURN { product, total, average, min_amt, max_amt }

Collecting Lists

Use COLLECT_LIST to aggregate values from the group into an array.

FOR u IN users
  COLLECT city = u.city
  AGGREGATE userNames = COLLECT_LIST(u.name)
  RETURN {
    city,
    userNames
  }

Restricting Group Items with KEEP

With INTO groups, each group array normally stores every variable in scope. Add KEEP v1, v2 to store only those variables — smaller results, no leaked document bodies.

FOR u IN users
  LET n = u.name
  COLLECT city = u.city INTO g KEEP n
  RETURN { city, names: g[*].n }

Without KEEP, each item of g would also carry u. Every name listed must be a variable in scope at the COLLECT — an unknown name is an error, not an empty group item.

COLLECT variable = expression [, ...]
[INTO groupVariable [KEEP var1, var2, ...]]
[WITH COUNT INTO countVariable]
[AGGREGATE variable = FUNC(expression) [, ...]]

Aggregation Functions

Basic Aggregations

SUM(arr)

Sum of all values in the array.

RETURN SUM([1, 2, 3, 4]) --10

AVG(arr)

Average of all values.

RETURN AVG([1, 2, 3, 4]) --2.5

MIN(arr)

Smallest value in the array.

RETURN MIN([5, 1, 9]) --1

MAX(arr)

Largest value in the array.

RETURN MAX([5, 1, 9]) --9

COUNT(arr)

Number of elements in the array.

RETURN COUNT([1, 2, 3]) --3

COUNT_DISTINCT(arr)

Number of unique elements.

RETURN COUNT_DISTINCT([1, 2, 2, 3]) --3

MEDIAN(arr)

Median value of the array.

RETURN MEDIAN([1, 5, 10]) --5

PERCENTILE(arr, p [, method])

Returns the p-th percentile (p in 0-100). method is "rank" (default) or "interpolation".

RETURN PERCENTILE([1..100], 95) --95
RETURN PERCENTILE([1, 2, 3, 4], 50, "interpolation") --2.5

Statistical Functions

VARIANCE(arr)

Population variance.

RETURN VARIANCE([1, 2, 3, 4, 5]) --2

VARIANCE_SAMPLE(arr)

Sample variance.

RETURN VARIANCE_SAMPLE([1, 2, 3, 4, 5]) --2.5

STDDEV(arr)

Sample standard deviation.

RETURN STDDEV([1, 2, 3, 4, 5]) --1.581...

STDDEV_POPULATION(arr)

Population standard deviation.

RETURN STDDEV_POPULATION([1, 2, 3, 4, 5]) --1.414...

Aggregation Example

FOR u IN users
  LET amounts = (FOR o IN orders FILTER o.user == u._key RETURN o.amount)
  RETURN {
    user: u.name,
    total: SUM(amounts),
    avg: ROUND(AVG(amounts), 2),
    count: COUNT(amounts),
    median: MEDIAN(amounts),
    stddev: ROUND(STDDEV(amounts), 2)
  }

Graph Queries String Functions