Mastering Elasticsearch Aggregations: Metric, Bucket, and Pipeline Queries in Practice

Aggregation Query Overview

Elasticsearch aggregation queries provide powerful data analysis capabilities, allowing extraction and calculation of complex statistics from indexed data. Three main aggregation types are supported: metric aggregations, bucket aggregations, and pipeline aggregations, each suited to specific use cases.

Aggregation Types

Metric Aggregations

Metric aggregations return numeric metrics based on field values, such as sum , avg , min , max , and stats . Typical scenarios include total sales, average order amount, or average session duration.

Bucket Aggregations

Bucket aggregations work like SQL GROUP BY, grouping documents into buckets based on field values, date intervals, or numeric ranges. Common bucket types are terms , date_histogram , and range . Example scenarios: count of articles per author, monthly sales analysis, or product count per price range.

Pipeline Aggregations

Pipeline aggregations take the output of other aggregations as input for further calculations. Frequently used pipeline types include avg_bucket , sum_bucket , and max/min_bucket . They enable tasks such as finding the month with the highest average sales or summing sales across price intervals.

Aggregation Application

Aggregations are typically combined with query clauses to filter the document set before aggregation. Nested aggregations allow building complex analysis pipelines by embedding one aggregation inside another.

doc_values vs. fielddata

Aggregations rely on either doc_values (column‑oriented storage on disk) or fielddata (in‑memory loading of text fields). Exact‑value fields (e.g., keyword) use doc_values by default, offering high performance for large datasets. Text fields use fielddata only when explicitly enabled, which can cause OOM on large data.

Handling Text Fields

Use .keyword sub‑field : Define a .keyword sub‑field for a text field to enable aggregations without loading fielddata.

Enable fielddata : Update the field mapping to enable fielddata, but only after careful resource impact assessment.

Performance Trade‑offs

Doc Values : Ideal for exact‑value and numeric fields; high‑performance because operations occur on disk‑based columnar data.

Fielddata : Allows aggregations on analyzed text but consumes large heap memory and may cause OOM.

Multi‑Fields (Multi‑Fields)

Elasticsearch allows a single field to be indexed in multiple ways. Adding a .keyword sub‑field to a text field enables both full‑text search and exact‑value aggregations, improving query efficiency.

Aggregation Query Examples

Terms Bucket Aggregation

Count articles per author and sort by article count:

POST /blog/_search
{
  "size": 0,
  "aggs": {
    "articles_per_author": {
      "terms": {
        "field": "author.keyword",
        "size": 10,
        "order": { "_count": "desc" }
      }
    }
  }
}

Date Histogram Aggregation

Analyze monthly sales record counts:

POST /sales/_search
{
  "size": 0,
  "aggs": {
    "sales_over_time": {
      "date_histogram": {
        "field": "sale_date",
        "calendar_interval": "month",
        "format": "yyyy-MM"
      }
    }
  }
}

Range Aggregation

Count products in different price ranges:

POST /products/_search
{
  "size": 0,
  "aggs": {
    "price_ranges": {
      "range": {
        "field": "price",
        "ranges": [
          { "to": 100 },
          { "from": 100, "to": 500 },
          { "from": 500 }
        ]
      }
    }
  }
}

Nested Aggregation

Calculate the average price per order when an order contains multiple products:

POST /orders/_search
{
  "size": 0,
  "aggs": {
    "orders": {
      "nested": { "path": "products" },
      "aggs": {
        "avg_price_per_order": {
          "avg": { "field": "products.price" }
        }
      }
    }
  }
}

Pipeline Aggregation (Top Sales Month)

Find the month with the highest total sales and compute its average sales:

POST /sales/_search
{
  "size": 0,
  "aggs": {
    "sales_over_time": {
      "date_histogram": { "field": "sale_date", "calendar_interval": "month" },
      "aggs": {
        "total_sales": { "sum": { "field": "amount" } },
        "top_sales_month": {
          "top_hits": { "sort": [{ "total_sales": { "order": "desc" } }], "size": 1 }
        },
        "avg_sales_top_month": { "avg_bucket": { "buckets_path": "total_sales" } }
      }
    }
  }
}

Derivative Aggregation

Compute daily sales growth rate:

POST /sales/_search
{
  "size": 0,
  "aggs": {
    "sales_over_time": {
      "date_histogram": { "field": "sale_date", "calendar_interval": "day" },
      "aggs": {
        "total_sales": { "sum": { "field": "amount" } },
        "sales_derivative": { "derivative": { "buckets_path": "total_sales" } }
      }
    }
  }
}

Cumulative Sum Aggregation

Calculate cumulative sales per month:

POST /sales/_search
{
  "size": 0,
  "aggs": {
    "sales_over_time": {
      "date_histogram": { "field": "sale_date", "calendar_interval": "month" },
      "aggs": {
        "total_sales": { "sum": { "field": "amount" } },
        "cumulative_sales": { "cumulative_sum": { "buckets_path": "total_sales" } }
      }
    }
  }
}

Moving Average Aggregation

Compute a 7‑day moving average of daily sales:

POST /sales/_search
{
  "size": 0,
  "aggs": {
    "sales_over_time": {
      "date_histogram": { "field": "sale_date", "calendar_interval": "day" },
      "aggs": {
        "total_sales": { "sum": { "field": "amount" } },
        "moving_avg_sales": { "moving_avg": { "buckets_path": "total_sales", "window": 7 } }
      }
    }
  }
}

Bucket Script Aggregation

Calculate each product’s sales percentage within a bucket:

POST /sales/_search
{
  "size": 0,
  "aggs": {
    "sales_by_product": {
      "terms": { "field": "product.keyword" },
      "aggs": {
        "total_sales": { "sum": { "field": "amount" } },
        "sales_percentage": {
          "bucket_script": {
            "buckets_path": { "thisSales": "total_sales", "totalSales": "_sum" },
            "script": "params.thisSales / params.totalSales * 100"
          }
        }
      }
    },
    "total_sales": { "sum": { "field": "amount" } }
  }
}

Filters Aggregation

Analyze sales by product category using filters:

POST /products/_search
{
  "size": 0,
  "aggs": {
    "sales_by_category": {
      "filters": {
        "filters": {
          "electronics": { "term": { "category": "electronics" } },
          "books": { "term": { "category": "books" } },
          "other": { "match_all": {} }
        }
      },
      "aggs": { "total_sales": { "sum": { "field": "price" } } }
    }
  }
}

Aggregation Sorting

Sort by _count to show buckets with the highest or lowest document counts.

Sort by _key for alphabetical or numeric ordering of bucket keys.

Optimization Recommendations

Avoid unnecessary large aggregations on massive datasets to reduce resource consumption.

Cache frequently executed aggregation results using Elasticsearch’s caching mechanism.

Design indices and mappings thoughtfully—choose appropriate field types, set suitable shard and replica counts.

Monitor performance metrics and logs (execution time, memory usage) to identify and address bottlenecks.