Aggregates
In addition to fetching multiple rows of raw data from a collection, the query API supports fetching aggregated data.
Aggregates are requested in the aggregates
field of the Query
object.
There are three types of aggregate:
single_column
aggregates apply an aggregation function (as defined by the column's scalar type in the schema response) to a column,column_count
aggregates count the number of rows with non-null values in the specified columns. If thedistinct
flag is set, then the count should only count unique non-null values of those columns,star_count
aggregates count all matched rows.
If the connector supports capability query.nested_fields.aggregates
then single_column
and column_count
aggregates may also reference nested fields within a column using the field_path
property.
Example
The following query object requests the aggregated sum of all order totals, along with the count of all orders, and the count of all orders which have associated invoices (via the nullable invoice_id
column):
{
"collection": ["orders"],
"collection_relationships": {},
"query": {
"aggregates": {
"orders_total": {
"type": "single_column",
"function": "sum",
"column": "total"
},
"invoiced_orders_count": {
"type": "column_count",
"columns": ["invoice_id"]
},
"orders_count": {
"type": "star_count"
}
}
}
}
In this case, the query has no predicate function, so all three aggregates would be computed over all rows.
Requirements
- Each aggregate should be computed over all rows that match the
Query
. - Each requested aggregate must be returned in the
aggregates
property on theQueryResponse
object, using the same key as used to request it.
See also
- Type
Aggregate