Aggregates

In addition to fetching multiple rows of raw data from a collection, the query API supports fetching aggregated data. If a connector wants to support aggregates, it needs to enable the query.aggregates capability. It also needs to return the capabilities.query.aggregates object from the schema to indicate which scalar type is used to return the result of count aggregates.

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 the distinct 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.

If the column referenced in single_column and column_count aggregates has arguments defined for it in the schema, then the arguments property is used to provide values for those arguments.

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 the QueryResponse object, using the same key as used to request it.

See also