Design

Aggregators

How to perform calculations on array data?
ENTERPRISE FEATURE Available for:
Carbone Cloud
Carbone On-premise
Embedded Carbone JS v4.0+

Overview

An aggregate formatter processes a set of values and returns a single aggregated result.
It can be used as a standalone expression or within loops to calculate custom groupings, such as sub-totals or cumulative totals.

The following aggregators are available:

aggSum: Calculates the sum of all values in a dataset.
aggAvg: Computes the average of values in a dataset.
aggMin: Finds the minimum value in a dataset.
aggMax: Finds the maximum value in a dataset.
aggCount: Counts the total number of items in a dataset.
aggCountD: Counts the number of distinct items in a dataset.
aggStr: Concatenates all strings in a dataset, using a specified separator.
aggStrD: Concatenates distinct strings in a dataset, using a specified separator.
cumSum: Calculates cumulative sums (running totals) as the dataset progresses through a series.
cumCount: Assigns a sequential integer to each row in a list.
cumCountD: Assigns a sequential integer to each distinct item in a list.

Aggregators have a limitation when accessing a value within a sub-object, such as {d[].sub.qty}.
To handle this issue, use :print formatter to aggregate values coming from sub-objects. For example: {d[]:print(.sub.qty):aggSum}

Learn with :aggSum(partitionBy)

Here is an example using :aggSum, which calculates and returns the total sum of all values in a dataset.

Carbone automatically iterates through the array and calculates the sum of all its elements if the [] brackets are left empty. Array filters can be used to reduce the dataset by applying specific conditions.

data

{
  "cars": [
    { "brand":"Lexus"   , "qty":1 , "sort":1 },
    { "brand":"Faraday" , "qty":4 , "sort":4 },
    { "brand":"Venturi" , "qty":3 , "sort":3 },
    { "brand":"Faraday" , "qty":2 , "sort":2 },
    { "brand":"Aptera"  , "qty":1 , "sort":1 },
    { "brand":"Venturi" , "qty":10, "sort":5 }
  ]
}

template

Total:
{d.cars[].qty:aggSum}Total with filters:
{d.cars[sort>1].qty:aggSum}Total with multiplication:
{d.cars[sort>1].qty:mul(.sort):aggSum:formatC}

result

Total: 21Total with filters: 19Total with multiplication: 79.00 €

Aggregate and Loop

Perform aggregation and looping simultaneously.

You can calculate and display the cumulative sum of values using :cumSum instead of :aggSum. This allows you to track the running total as you iterate through the data.

data

[
  { "brand":"Lexus"   , "qty":1  },
  { "brand":"Faraday" , "qty":4  },
  { "brand":"Venturi" , "qty":3  },
  { "brand":"Faraday" , "qty":2  },
  { "brand":"Aptera"  , "qty":1  },
  { "brand":"Venturi" , "qty":10 }
]

template

BrandTotalCumulative{d[i].brand}{d[i].qty:aggSum}{d[i].qty:cumSum}{d[i+1].brand}

result

BrandTotalCumulativeLexus211Faraday215Venturi218Faraday2110Aptera2111Venturi2121

Aggregate for independent groups

Carbone aggregators support one or more parameters that allow you to divide data into separate groups (or "partitions") for independent calculations. This functionality is similar to the PARTITION BY clause in SQL.

By specifying grouping parameters, you can ensure that the aggregation is performed independently for each defined group, making it easier to generate structured reports with grouped totals, averages, or other metrics.

data

[
  { "brand":"Lexus"   , "qty":1  },
  { "brand":"Faraday" , "qty":4  },
  { "brand":"Venturi" , "qty":3  },
  { "brand":"Faraday" , "qty":2  },
  { "brand":"Aptera"  , "qty":1  },
  { "brand":"Venturi" , "qty":10 }
]

template

BrandTotal by brand{d[i].brand}{d[i].qty:aggSum(.brand)}{d[i+1].brand}

result

BrandTotal by brandLexus1Faraday6Venturi13Faraday6Aptera1Venturi13

Aggregator with nested arrays

Calculating subtotals for nested arrays.

data

[
  { 
    "country" : "France",
    "cities" : [
      { "id" : "Paris"     , "cars" : 100 },
      { "id" : "Nantes"    , "cars" : 50  },
      { "id" : "Pouzauges" , "cars" : 1   }
    ]
  },
  { 
    "country" : "Italy",
    "cities" : [
      { "id" : "Rome"   , "cars" : 20 },
      { "id" : "Venise" , "cars" : 2 }
    ]
  }
]

template

BrandTotal per city{d[i].country}{d[i].cities[].cars:aggSum}{d[i+1].country}

result

BrandTotal per cityFrance151Italy22

:aggAvg(partitionBy)

The :aggAvg aggregator calculates and returns the average of a dataset.

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

data

{
  "cars": [
    { "brand":"Lexus"   , "qty":1 , "sort":1 },
    { "brand":"Faraday" , "qty":4 , "sort":4 },
    { "brand":"Venturi" , "qty":3 , "sort":3 },
    { "brand":"Faraday" , "qty":2 , "sort":2 },
    { "brand":"Aptera"  , "qty":1 , "sort":1 },
    { "brand":"Venturi" , "qty":10, "sort":5 }
  ]
}

template

Average:
{d.cars[].qty:aggAvg}Average with filters:
{d.cars[sort>1].qty:aggAvg}Average with multiplication:
{d.cars[sort>1].qty:mul(.sort):aggAvg:formatC}

result

Average: 3.5Average with filters: 4.75Average with multiplication: 19.75 €

:aggMin(partitionBy)

The :aggMin aggregator returns the minimum value of a dataset.

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

data

{
  "cars": [
    { "brand":"Lexus"   , "qty":1 , "sort":1 },
    { "brand":"Faraday" , "qty":4 , "sort":4 },
    { "brand":"Venturi" , "qty":3 , "sort":3 },
    { "brand":"Faraday" , "qty":2 , "sort":2 },
    { "brand":"Aptera"  , "qty":1 , "sort":1 },
    { "brand":"Venturi" , "qty":10, "sort":5 }
  ]
}

template

Minimum:
{d.cars[].qty:aggMin}Minimum with filters:
{d.cars[sort>1].qty:aggMin}

result

Minimum: 1Minimum with filters: 2

:aggMax(partitionBy)

The :aggMax aggregator returns the maximum value of a dataset.

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

data

{
  "cars": [
    { "brand":"Lexus"   , "qty":1 , "sort":1 },
    { "brand":"Faraday" , "qty":4 , "sort":4 },
    { "brand":"Venturi" , "qty":3 , "sort":3 },
    { "brand":"Faraday" , "qty":2 , "sort":2 },
    { "brand":"Aptera"  , "qty":1 , "sort":1 },
    { "brand":"Venturi" , "qty":10, "sort":5 }
  ]
}

template

Maximum:
{d.cars[].qty:aggMax}Maximum with filters:
{d.cars[sort>1].qty:aggMax}

result

Maximum: 10Maximum with filters: 10

:aggCount(partitionBy)

The :aggCount aggregator returns the number of items of a dataset.

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

It counts items regardless of the value passed on the left side of the formatter (e.g., {d[i].qty} in the example). The value is ignored by the formatter.

data

{
  "cars": [
    { "brand":"Lexus"   , "qty":1 , "sort":1 },
    { "brand":"Faraday" , "qty":4 , "sort":4 },
    { "brand":"Venturi" , "qty":3 , "sort":3 },
    { "brand":"Faraday" , "qty":2 , "sort":2 },
    { "brand":"Aptera"  , "qty":1 , "sort":1 },
    { "brand":"Venturi" , "qty":10, "sort":5 }
  ]
}

template

Number of items:
{d.cars[].qty:aggCount}Number of items with filters:
{d.cars[sort>1].qty:aggCount}

result

Number of items: 6Number of items with filters: 4

:aggCountD(partitionBy)

The :aggCountD aggregator calculates and returns the number of Distinct items in a dataset.

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

data

{
  "cars": [
    { "brand":"Lexus"   , "qty":1 , "sort":1 },
    { "brand":"Faraday" , "qty":4 , "sort":4 },
    { "brand":"Venturi" , "qty":3 , "sort":3 },
    { "brand":"Faraday" , "qty":2 , "sort":2 },
    { "brand":"Aptera"  , "qty":1 , "sort":1 },
    { "brand":"Venturi" , "qty":10, "sort":5 }
  ]
}

template

Number of brands:
{d.cars[].brand:aggCountD}

result

Number of brands: 4

:aggStr(separator, partitionBy)

The :aggStr aggregator concatenates all values in a dataset, separated by ", " (the default separator).

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

data

{
  "cars": [
    { "brand":"Lexus"   , "qty":1 , "sort":1 },
    { "brand":"Faraday" , "qty":4 , "sort":4 },
    { "brand":"Venturi" , "qty":3 , "sort":3 },
    { "brand":"Faraday" , "qty":2 , "sort":2 },
    { "brand":"Aptera"  , "qty":1 , "sort":1 },
    { "brand":"Venturi" , "qty":10, "sort":5 }
  ]
}

template

Cars list:{d.cars[].brand:aggStr}{d.cars[].brand:aggStr(' | ')}

result

Cars list:Lexus, Faraday, Venturi, Faraday, Aptera, VenturiLexus | Faraday | Venturi | Faraday | Aptera | Venturi

:aggStrD(separator, partitionBy)

The :aggStrD aggregator concatenates all Distinct values in a dataset, separated by ", " (the default separator).

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

data

{
  "cars": [
    { "brand":"Lexus"   , "qty":1 , "sort":1 },
    { "brand":"Faraday" , "qty":4 , "sort":4 },
    { "brand":"Venturi" , "qty":3 , "sort":3 },
    { "brand":"Faraday" , "qty":2 , "sort":2 },
    { "brand":"Aptera"  , "qty":1 , "sort":1 },
    { "brand":"Venturi" , "qty":10, "sort":5 }
  ]
}

template

Cars list:{d.cars[].brand:aggStrD}{d.cars[].brand:aggStrD(' | ')}

result

Cars list:Lexus, Faraday, Aptera, VenturiLexus | Faraday | Aptera | Venturi

:cumSum(partitionBy)

The :cumSum formatter, also called a running total, calculates the cumulative sum of data by continuously adding values as it moves through the series.

data

[
  { "brand":"Lexus"   , "qty":1  },
  { "brand":"Faraday" , "qty":4  },
  { "brand":"Venturi" , "qty":3  },
  { "brand":"Faraday" , "qty":2  },
  { "brand":"Aptera"  , "qty":1  },
  { "brand":"Venturi" , "qty":10 }
]

template

BrandRunning total{d[i].brand}{d[i].qty:cumSum}{d[i+1].brand}

result

BrandRunning totalLexus1Faraday5Venturi8Faraday10Aptera11Venturi21

:cumCount(partitionBy)

The :cumCount formatter returns a sequential integer to each row in a list.

A dynamic variable, partition, can be passed to :cumCount(partition). This ensures the row numbering resets to 1 for each partition and increments sequentially within that partition.

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

It counts items regardless of the value passed on the left side of the formatter (e.g., {d[i].qty} in the example). The value is ignored by the formatter.

data

[
  { "brand":"Lexus"   , "qty":1  },
  { "brand":"Faraday" , "qty":4  },
  { "brand":"Faraday" , "qty":2  },
  { "brand":"Faraday" , "qty":2  },
  { "brand":"Venturi" , "qty":3  },
  { "brand":"Venturi" , "qty":3  },
  { "brand":"Aptera"  , "qty":1  },
  { "brand":"Venturi" , "qty":10 }
]

template

BrandCountCount by brand{d[i].brand}{d[i].qty:cumCount}{d[i].qty:cumCount(.brand)}{d[i+1].brand}

result

BrandCountCount by brandLexus11Faraday21Faraday32Faraday43Venturi51Venturi62Aptera71Venturi83

:cumCountD(partitionBy)

The :cumCountD counts the number of Distinct items in a list.

For more details on using aggregators and partitioning, refer to the examples provided for :aggSum.

data

[
  { "brand":"Lexus"   , "qty":1  },
  { "brand":"Faraday" , "qty":4  },
  { "brand":"Faraday" , "qty":2  },
  { "brand":"Venturi" , "qty":3  },
  { "brand":"Aptera"  , "qty":1  },
  { "brand":"Venturi" , "qty":10 }
]

template

BrandCount{d[i].brand}{d[i].brand:cumCountD}{d[i+1].brand}

result

BrandCountLexus1Faraday2Faraday2Venturi3Aptera4Venturi4

:aggSum:cumSum

The :aggSum and :cumSum formatters can be used together in specific cases, such as calculating the total number of cars per country while also displaying a running total.

data

[
  { 
    "country" : "France",
    "cities" : [
      { "id" : "Paris"     , "cars" : 100 },
      { "id" : "Nantes"    , "cars" : 50  },
      { "id" : "Pouzauges" , "cars" : 1   }
    ]
  },
  { 
    "country" : "Italy",
    "cities" : [
      { "id" : "Rome"   , "cars" : 20 },
      { "id" : "Venise" , "cars" : 2 }
    ]
  }
]

template

BrandRunning total{d[i].country}{d[i].cities[].cars:aggSum:cumSum}{d[i+1].country}

result

BrandRunning totalFrance151Italy173