06b — Nested and multi-level aggregations

A single aggregation level answers one question. Multi-level nesting answers many simultaneously. This part covers composing bucket and metric aggregations into hierarchies, scoping with filter, diving into nested objects with the nested aggregation, and climbing back to the parent with reverse_nested.

Goals for this part

6.3 — Nested aggregations (multi-level)

6.3.1 Metric inside bucket — average order value per country

GET shop-orders-demo/_search
{
  "size": 0,
  "aggs": {
    "by_country": {
      "terms": {
        "field": "delivery.country_code",
        "size": 10
      },
      "aggs": {
        "avg_order_value": {
          "avg": { "field": "total_amount" }
        },
        "total_revenue": {
          "sum": { "field": "total_amount" }
        }
      }
    }
  }
}

Each country bucket now carries its own avg_order_value and total_revenue.

6.3.2 Bucket inside bucket — channel → monthly orders

"aggs": {
  "by_channel": {
    "terms": { "field": "channel", "size": 5 },
    "aggs": {
      "orders_over_time": {
        "date_histogram": {
          "field": "created_at",
          "calendar_interval": "month",
          "min_doc_count": 0
        }
      }
    }
  }
}

Nesting depth is not limited by Elasticsearch, but every extra level multiplies the number of buckets and memory consumption. Two or three levels is typical in production.

6.3.3 The filter aggregation — scoped sub-bucket

Use filter to compute a metric on a subset of a bucket’s documents without introducing a full new bucket axis:

"aggs": {
  "by_country": {
    "terms": { "field": "delivery.country_code" },
    "aggs": {
      "high_value_orders": {
        "filter": { "range": { "total_amount": { "gte": 500 } } },
        "aggs": {
          "avg_high_value": { "avg": { "field": "total_amount" } }
        }
      }
    }
  }
}

6.3.4 The nested aggregation — diving into items

The items field is mapped as nested (each item is an isolated document internally). You cannot aggregate across nested objects with a flat terms — you must wrap the inner agg in a nested context.

GET shop-orders-demo/_search
{
  "size": 0,
  "aggs": {
    "into_items": {
      "nested": { "path": "items" },
      "aggs": {
        "by_category": {
          "terms": {
            "field": "items.category",
            "size": 20
          },
          "aggs": {
            "total_units_sold": {
              "sum": { "field": "items.quantity" }
            },
            "total_item_revenue": {
              "sum": { "field": "items.unit_price" }
            }
          }
        }
      }
    }
  }
}

Note on line-total arithmetic: Elasticsearch does not have a native multiply-then-sum agg. Options are: (a) pre-compute items.line_total = unit_price * quantity at index time (recommended for performance), then sum on it; or (b) use a script in the metric agg ("script": "doc['items.unit_price'].value * doc['items.quantity'].value"). Scripts work but disable doc_values optimisations — see ch07 for performance impact.

reverse_nested — climbing back to the parent document

After descending into items, use reverse_nested to access parent-level fields:

"aggs": {
  "into_items": {
    "nested": { "path": "items" },
    "aggs": {
      "by_category": {
        "terms": { "field": "items.category", "size": 10 },
        "aggs": {
          "back_to_order": {
            "reverse_nested": {},
            "aggs": {
              "avg_order_total": {
                "avg": { "field": "total_amount" }
              },
              "unique_customers": {
                "cardinality": { "field": "customer.id" }
              }
            }
          }
        }
      }
    }
  }
}

This produces: for each item category, the average order total and unique customer count — linking item-level bucketing back to order-level metrics.

Lab — Multi-level and nested aggregations

Part A: Multi-level buckets and metrics

Goal: Answer multi-dimensional business questions with nested agg hierarchies.

Step 1 — Revenue by loyalty tier and country. Two-level terms: outer on customer.loyalty_tier, inner on delivery.country_code. Add sum and avg of total_amount for each country, and a cardinality of customer.id. Use size: 0 and scope to the last 12 months.

GET /shop-orders-demo/_search
{
  "size": 0,
  "query": {
    "range": { "created_at": { "gte": "now-12M" } }
  },
  "aggs": {
    "by_loyalty_tier": {
      "terms": { "field": "customer.loyalty_tier", "size": 10 },
      "aggs": {
        "by_country": {
          "terms": { "field": "delivery.country_code", "size": 5 },
          "aggs": {
            "total_revenue":    { "sum":         { "field": "total_amount" } },
            "avg_order_value":  { "avg":         { "field": "total_amount" } },
            "unique_customers": { "cardinality": { "field": "customer.id"  } }
          }
        }
      }
    }
  }
}

Questions: 1. Which loyalty tier generates the most revenue? 2. For that tier, which country has the highest average order value? 3. Notice how sum_other_doc_count grows as the inner bucket size is small. What does this mean?

Step 2 — Add a filter sub-aggregation inside the by_country bucket to compute the average value of orders over 500 (high-value), alongside the overall average:

GET /shop-orders-demo/_search
{
  "size": 0,
  "aggs": {
    "by_country": {
      "terms": { "field": "delivery.country_code", "size": 10 },
      "aggs": {
        "avg_all":         { "avg": { "field": "total_amount" } },
        "high_value_orders": {
          "filter": { "range": { "total_amount": { "gte": 500 } } },
          "aggs": {
            "avg_high_value": { "avg": { "field": "total_amount" } },
            "count":          { "value_count": { "field": "total_amount" } }
          }
        }
      }
    }
  }
}

Questions: 1. In how many countries do high-value orders (>=500) represent a significant share? 2. How does the average high-value order compare to the overall average per country?

Part B: Nested and reverse_nested aggregations

Goal: Aggregate across the nested items field and link results back to parent orders.

Step 3 — Dive into the items nested path. Count total units sold and total item revenue per category (top 15):

GET /shop-orders-demo/_search
{
  "size": 0,
  "aggs": {
    "into_items": {
      "nested": { "path": "items" },
      "aggs": {
        "by_category": {
          "terms": {
            "field": "items.category",
            "size": 15
          },
          "aggs": {
            "total_units_sold":    { "sum": { "field": "items.quantity" } },
            "total_item_revenue":  { "sum": { "field": "items.unit_price" } }
          }
        }
      }
    }
  }
}

Questions: 1. Which product category has the most units sold? 2. Which category generates the most revenue?

Step 4 — Extend with reverse_nested to compute the average parent order total and unique customer count per category:

GET /shop-orders-demo/_search
{
  "size": 0,
  "aggs": {
    "into_items": {
      "nested": { "path": "items" },
      "aggs": {
        "by_category": {
          "terms": { "field": "items.category", "size": 10 },
          "aggs": {
            "total_units_sold": { "sum": { "field": "items.quantity" } },
            "back_to_order": {
              "reverse_nested": {},
              "aggs": {
                "avg_order_total":  { "avg":         { "field": "total_amount" } },
                "unique_customers": { "cardinality": { "field": "customer.id"  } }
              }
            }
          }
        }
      }
    }
  }
}

Questions: 1. In which category do buyers tend to place the highest-value overall orders? 2. Which category has the most unique customers? 3. Explain in your own words why reverse_nested is needed here — what goes wrong without it?

Step 5 — Try removing the nested wrapper and running a flat terms on items.category directly. Observe the error or unexpected result:

GET /shop-orders-demo/_search
{
  "size": 0,
  "aggs": {
    "flat_attempt": {
      "terms": { "field": "items.category", "size": 10 }
    }
  }
}

What happens? Does it work? If it returns results, can you verify whether they are correct?

Takeaway: The nested aggregation context is required to cross into nested objects — without it, queries and aggregations either fail or produce incorrect results. reverse_nested lets you bridge back to parent-level fields after descending. Nesting agg hierarchies more than 2–3 levels deep multiplies bucket counts quickly — measure memory impact before going deep.