Aggregation API

6 min

Overview

The Aggregate API offers a series of functions to execute aggregations on a Table, rather than utilising the Table API. Whilst the Table API will still be used to get information from records, this API focuses much more on aggregate functions to make sure it is much more efficient and easier to use for specific purposes.

Tables can be accessed by using this generic notation:

Aggregate('User')

Where ‘User’ can be replaced with any Table name.

Alternatively it can be accessed by the Table api as well

Table('User').getAggregate()

Querying data

Aggregate operators

The following table is a list of the supported queries. For filtering, see Table Api

Operators
COUNT()	The simplest operator implementing to count records. For example: Aggregate("User").COUNT()
COUNT(fieldName)	Count the non null occurrences of a field: Aggregate("User").COUNT("Email")
DISTINCT(fieldName)	Performs a count of the Unique values (e.g. the count of the unique Companies referenced from the User table) Aggregate("User").DISTINCT("Company")
SUM(fieldName)	Count the non null occurrences of a field: Aggregate("User").SUM("FailedLoginAttempts")
MIN(fieldName)	Minimum of a field values: Aggregate("User").MIN("FailedLoginAttempts")
MAX(fieldName)	Maximum of a field values: Aggregate("User").MAX("FailedLoginAttempts")
AVG(fieldName)	Compute the average o field values: Aggregate("User").AVG("FailedLoginAttempts")
HAVING( CRITERION(“AGGREGATE_NAME“, value) )	Allows aggregates to be filtered from the results: Aggregate("Trigger") .GROUP_BY("TriggerWhen", AS("When")) .COUNT("TriggerWhen", AS("Total")) // This is the Total column .HAVING(GT("Total", 60)) // Allow groups with 'Total' greater than 60 .fetch();

Aggregations can be aliased:

Aggregate("User").COUNT(As("UserCount");Aggregate("User").COUNT("Email", AS("EmailCount"))

Aggregations can leverage the query operators from the Table Api

Aggregate("User").COUNT().EQUAL("Active", true)Aggregate("User").COUNT().AND( EQUAL("Active", true), GT("FailedLoginAttempts", 0))

Grouping

You can add group by fields to the Aggregate:

Aggregate("Trigger")
    .IN("Table", ["Incident", "ITSMRequest"])
    .GROUP_BY("Table")
    .GROUP_BY("TriggerWhen", AS("When"))
    .COUNT("TriggerWhen", AS("Total"))
    .fetch();

Results in:

[
  {
    "TableGroup": "ITSMRequest",
    "TableGroupDisplayValue": "ITSM Request",
    "Total": 2,
    "When": "after",
    "WhenDisplayValue": "After"
  },
  {
    "TableGroup": "Incident",
    "Total": 6,
    "When": "before",
    "WhenDisplayValue": "Before"
  },
  {
    "TableGroup": "ITSMRequest",
    "TableGroupDisplayValue": "ITSM Request",
    "Total": 5,
    "When": "before",
    "WhenDisplayValue": "Before"
  },
  {
    "TableGroup": "Incident",
    "Total": 4,
    "When": "after",
    "WhenDisplayValue": "After"
  }
]

Querying example

let aggregateResult = Aggregate('SLA')
.COUNT(AS('Breached'))
.SUM("TargetDuration")
.EQUAL('ProgressLevel', 'Breached');
.fetchSingle();

// {Breached=4, TargetDurationSum=115320000}
aggregateResult;

// 4
aggregateResult.Breached;