Sub-aggregations

This is where aggregations get really powerful. We can put aggs inside other aggs — bucket > metric, bucket > bucket > metric, and so on. The standard analytics pattern in ES is “split docs into buckets, then compute metrics per bucket.”

In simple language — every bucket agg can have an aggs block of its own. That inner block runs once per bucket.

The basic pattern — bucket + metric

“Average order value per category”:

GET /orders/_search
{
  "size": 0,
  "aggs": {
    "by_category": {
      "terms": { "field": "category.keyword" },
      "aggs": {
        "avg_amount": {
          "avg": { "field": "amount" }
        }
      }
    }
  }
}

Response:

{
  "aggregations": {
    "by_category": {
      "buckets": [
        { "key": "laptops", "doc_count": 142, "avg_amount": { "value": 1240.50 } },
        { "key": "phones",  "doc_count": 98,  "avg_amount": { "value": 820.00  } },
        { "key": "tablets", "doc_count": 47,  "avg_amount": { "value": 540.75  } }
      ]
    }
  }
}

In SQL terms — SELECT category, AVG(amount) FROM orders GROUP BY category. The aggs inside is what makes the analogy work.

Multiple metrics per bucket

We can stack as many metrics inside a bucket as we want:

{
  "aggs": {
    "by_category": {
      "terms": { "field": "category.keyword" },
      "aggs": {
        "total_revenue": { "sum": { "field": "amount" } },
        "avg_order":     { "avg": { "field": "amount" } },
        "biggest_order": { "max": { "field": "amount" } },
        "order_count":   { "value_count": { "field": "amount" } }
      }
    }
  }
}

Each bucket now returns four numbers. Equivalent to SELECT category, SUM(amount), AVG(amount), MAX(amount), COUNT(amount) GROUP BY category.

Nesting buckets — bucket > bucket > metric

This is where it gets fun. “Daily revenue per category”:

GET /orders/_search
{
  "size": 0,
  "aggs": {
    "daily": {
      "date_histogram": {
        "field": "created_at",
        "calendar_interval": "day"
      },
      "aggs": {
        "by_category": {
          "terms": { "field": "category.keyword" },
          "aggs": {
            "revenue": { "sum": { "field": "amount" } }
          }
        }
      }
    }
  }
}

Now we get a tree — for each day, for each category, the revenue. Perfect for a stacked-bar chart.

Ordering buckets by sub-agg values

By default, terms orders buckets by doc_count desc. We can order by a sub-metric instead — “top 5 categories by total revenue”:

{
  "aggs": {
    "by_category": {
      "terms": {
        "field": "category.keyword",
        "size": 5,
        "order": { "revenue": "desc" }
      },
      "aggs": {
        "revenue": { "sum": { "field": "amount" } }
      }
    }
  }
}

For ordering by stats sub-aggs, use dot notation — { "stats_agg.avg": "desc" }.

A realistic dashboard query

Putting it all together — “monthly active users by country, last 90 days”:

GET /events/_search
{
  "size": 0,
  "query": {
    "range": { "@timestamp": { "gte": "now-90d/d" } }
  },
  "aggs": {
    "monthly": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "month"
      },
      "aggs": {
        "by_country": {
          "terms": { "field": "country.keyword", "size": 10 },
          "aggs": {
            "unique_users": {
              "cardinality": { "field": "user_id" }
            },
            "top_actions": {
              "terms": { "field": "action.keyword", "size": 3 }
            }
          }
        }
      }
    }
  }
}

This single request gives us — for each of the last 3 months, for each of the top 10 countries, the unique user count AND the top 3 actions. A whole dashboard panel in one query.

Performance considerations

Sub-aggregations multiply work. A terms agg with 50 buckets, each with another terms with 50 sub-buckets, means 2,500 buckets in memory. ES applies safety limits (search.max_buckets — default 65,536). Hit it, get an error.

Tips:

• Keep size reasonable. Don’t ask for 10,000 sub-buckets unless you really need them.
• For high-cardinality fields, consider sampling with the sampler agg.
• Cardinality sub-aggs are cheap (HyperLogLog). Terms sub-aggs are expensive at scale.
• Prefer filter-context queries above the agg block — fewer docs flow into aggs.

Quick rules

• Bucket > metric — the basic split-apply pattern.
• Bucket > bucket > metric — the typical analytics dashboard pattern.
• order: { sub_agg: "desc" } — sort buckets by a sub-metric.
• Mind search.max_buckets for deeply nested aggs.
• Filter the result set with a query first, then aggregate — way more efficient than aggregating then post-filtering.