Aggregations

Execute complex data grouping and aggregation operations.

POST /api/datasets/{id}/_search-aggregates

Execute a search with aggregations in visual format.

Authentication: Required

Path Parameters:

Parameter	Type	Description
id	string	Dataset ID or slug

Request Body:

Field	Type	Description
query	object	Elasticsearch query DSL
aggregations	array	Array of aggregation strings (function:field)
_source	array	Fields to return
sort	array	Sort configuration
from	integer	Offset
size	integer	Result count
report	string	Report ID for field selection
searchType	string	Elasticsearch search type
alias	string	Snapshot alias

Example Request:

{
  "query": {
    "range": {
      "order_date": {
        "gte": "2024-01-01"
      }
    }
  },
  "aggregations": [
    "sum:amount",
    "avg:amount",
    "count:order_id",
    "min:amount",
    "max:amount"
  ]
}

Response:

{
  "aggregations": {
    "sum_amount": {
      "value": 487500.50
    },
    "avg_amount": {
      "value": 3125.65
    },
    "count_order_id": {
      "value": 156
    },
    "min_amount": {
      "value": 50.00
    },
    "max_amount": {
      "value": 25000.00
    }
  },
  "hits": {
    "total": { "value": 156 },
    "hits": []
  }
}

Supported Aggregation Functions:

• sum:field - Sum of values
• avg:field - Average of values
• min:field - Minimum value
• max:field - Maximum value
• count:field - Count of records
• cardinality:field - Unique value count

---

POST /api/datasets/{id}/group

Group data with hierarchical aggregation.

Authentication: Required

Path Parameters:

Parameter	Type	Description
id	string	Dataset ID or slug

Request Body:

Field	Type	Description
groups	array	Array of grouping fields
aggs	object	Aggregation configuration
body	object	Query body (filter criteria)
parents	array	Parent group values (for drill-down)
level	integer	Current grouping level
timezone	string	Timezone for date grouping

Example Request (single-level grouping):

{
  "groups": ["region"],
  "aggs": {
    "total_amount": {
      "sum": { "field": "amount" }
    },
    "avg_amount": {
      "avg": { "field": "amount" }
    }
  },
  "body": {
    "query": {
      "range": {
        "order_date": { "gte": "2024-01-01" }
      }
    }
  }
}

Response:

{
  "total": 4,
  "buckets": [
    {
      "key": "West",
      "doc_count": 89,
      "total_amount": { "value": 178500.00 },
      "avg_amount": { "value": 2005.62 }
    },
    {
      "key": "East",
      "doc_count": 67,
      "total_amount": { "value": 165300.50 },
      "avg_amount": { "value": 2467.17 }
    }
  ]
}

Example Request (hierarchical grouping):

{
  "groups": ["region", "category"],
  "level": 0,
  "parents": [],
  "aggs": {
    "revenue": {
      "sum": { "field": "amount" }
    }
  }
}

Hierarchical Response:

{
  "buckets": [
    {
      "key": "West",
      "doc_count": 89,
      "revenue": { "value": 178500.00 },
      "children": [
        {
          "key": "Electronics",
          "doc_count": 45,
          "revenue": { "value": 95000.00 }
        },
        {
          "key": "Furniture",
          "doc_count": 44,
          "revenue": { "value": 83500.00 }
        }
      ]
    }
  ]
}

---

POST /api/datasets/{id}/group-rows

Apply grouping filter to rows (drill-down into specific group).

Authentication: Required

Path Parameters:

Parameter	Type	Description
id	string	Dataset ID or slug

Request Body:

Field	Type	Description
timezone	string	Timezone for date fields
groups	array	Grouping configuration
parents	array	Parent group values to filter by
body	object	Base query

Example Request:

{
  "groups": ["region", "category"],
  "parents": ["West", "Electronics"],
  "body": {
    "query": {
      "range": {
        "order_date": { "gte": "2024-01-01" }
      }
    }
  }
}

Response:

Returns filtered rows matching the group criteria:

{
  "total": 45,
  "hits": [
    {
      "order_id": "ORD-001",
      "region": "West",
      "category": "Electronics",
      "amount": 1500.00
    },
    {
      "order_id": "ORD-023",
      "region": "West",
      "category": "Electronics",
      "amount": 2300.00
    }
  ]
}

---

Aggregation Examples

Sales Summary

{
  "aggregations": [
    "sum:amount",
    "count:order_id",
    "avg:amount",
    "cardinality:customer_id"
  ]
}

Group by Region and Date

{
  "groups": ["region"],
  "aggs": {
    "daily_sales": {
      "date_histogram": {
        "field": "order_date",
        "calendar_interval": "day"
      },
      "aggs": {
        "revenue": {
          "sum": { "field": "amount" }
        }
      }
    }
  },
  "timezone": "America/New_York"
}

Top Products by Category

{
  "groups": ["category"],
  "aggs": {
    "top_products": {
      "terms": {
        "field": "product_name",
        "size": 10,
        "order": { "revenue": "desc" }
      },
      "aggs": {
        "revenue": {
          "sum": { "field": "amount" }
        }
      }
    }
  }
}

---

Best Practices

Performance

• Index grouping fields - Especially for terms aggregations
• Limit bucket size - Don't group by high-cardinality fields
• Use filters - Reduce data before aggregating
• Consider caching - Cache aggregation results when possible

Accuracy

• Use appropriate functions - Sum vs. avg vs. count
• Handle nulls - Filter out or account for null values
• Check precision - Elasticsearch approximations for large datasets
• Validate results - Compare with source data

Hierarchical Grouping

• Limit levels - 2-3 levels maximum for performance
• Progressive loading - Load children on demand
• Parent context - Pass parent values for drill-down
• Breadcrumbs - Track user navigation through hierarchy