Aggregations and Faceted Search: terms, range, date_histogram, nested and post_filter

Version note: The examples in this post were run with Elasticsearch 9.5.6, .NET 10 and Elastic.Clients.Elasticsearch 9.5.3, in the lab we built in post 2, on the 20,035-book books index.

Guess First

We are building the left side of the search page for Bookstore, the online bookstore used as the shared example across this site's series: category, language, price range and rating boxes, with the number of books next to each option. The user searched for "roman" and ticked "Historical Fiction" in the category box. We put the aggregations that produce the counts into the same request and add the selected category to the bool's filter, as in post 6:

GET /books/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [ { "match": { "description.tr": "roman" } } ],
      "filter": [ { "term": { "categories": "Historical Fiction" } } ]
    }
  },
  "aggs": {
    "categories": { "terms": { "field": "categories" } },
    "prices": { "range": { "field": "price", "ranges": [ { "key": "0-200", "to": 200 }, { "key": "200-400", "from": 200, "to": 400 }, { "key": "400+", "from": 400 } ] } }
  }
}

What does the user see in the category box? Are the other options, like "Fiction 9, Classics 8", still there? Which books does the price box count: all books matching "roman", or only those in the selected category?

Bucket and Metric Aggregations

Aggregations are computed in the same request as the search results, over the documents the query matched. There are two main kinds: bucket aggregations split documents into groups (terms, range, date_histogram, histogram), metric aggregations compute a number (avg, sum, min, max, cardinality). First, the "roman" search without filters:

GET /books/_search?filter_path=hits.total,aggregations
{
  "size": 0,
  "query": { "match": { "description.tr": "roman" } },
  "aggs": {
    "categories": { "terms": { "field": "categories" } },
    "languages": { "terms": { "field": "language" } },
    "prices": { "range": { "field": "price", "ranges": [ { "key": "0-200", "to": 200 }, { "key": "200-400", "from": 200, "to": 400 }, { "key": "400+", "from": 400 } ] } },
    "avg_price": { "avg": { "field": "price" } },
    "years": { "date_histogram": { "field": "publishedOn", "calendar_interval": "year", "format": "yyyy", "min_doc_count": 1 } }
  }
}
{"hits":{"total":{"value":9,"relation":"eq"}},"aggregations":{"categories":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":"Fiction","doc_count":9},{"key":"Classics","doc_count":8},{"key":"Historical Fiction","doc_count":1},{"key":"Science Fiction","doc_count":1}]},"languages":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":"tr","doc_count":9}]},"prices":{"buckets":[{"key":"0-200","to":200.0,"doc_count":5},{"key":"200-400","from":200.0,"to":400.0,"doc_count":3},{"key":"400+","from":400.0,"doc_count":1}]},"avg_price":{"value":267.6666666666667},"years":{"buckets":[{"key_as_string":"2016","key":1451606400000,"doc_count":1},{"key_as_string":"2018","key":1514764800000,"doc_count":1},{"key_as_string":"2019","key":1546300800000,"doc_count":1},{"key_as_string":"2020","key":1577836800000,"doc_count":1},{"key_as_string":"2021","key":1609459200000,"doc_count":3},{"key_as_string":"2022","key":1640995200000,"doc_count":1},{"key_as_string":"2023","key":1672531200000,"doc_count":1}]}}}

"size": 0 asks for the aggregations only, not the result list. The category counts add up to more (19) than the number of books (9), because categories is a multi-valued field and one book falls into several buckets; in a UI that is normal and expected. In range, from is inclusive and to exclusive: a book priced at 200 lira is counted in "200-400". min_doc_count: 1 in date_histogram skips empty years; leave it at 0 if you want to show the gaps when drawing a chart.

Aggregations only work on fields with doc values, such as keyword, numbers and dates. We saw in post 3 that trying terms on a text field like title ends with a fielddata error; the .keyword subfields in the mapping exist for this.

Nested Aggregations: Authors

Authors are nested, so an aggregation at the root cannot reach them; you first step down into the author documents with a nested aggregation and, if needed, step back up to the book with reverse_nested. Across the whole catalog, the author roles, the number of distinct authors and the average book price of the three authors with the most books:

GET /books/_search?filter_path=aggregations
{
  "size": 0,
  "aggs": {
    "authors": {
      "nested": { "path": "authors" },
      "aggs": {
        "roles": { "terms": { "field": "authors.role" } },
        "distinct_names": { "cardinality": { "field": "authors.name.keyword" } },
        "top": {
          "terms": { "field": "authors.name.keyword", "size": 3 },
          "aggs": { "books": { "reverse_nested": {}, "aggs": { "avg_price": { "avg": { "field": "price" } } } } }
        }
      }
    }
  }
}
{"aggregations":{"authors":{"doc_count":20041,"top":{"doc_count_error_upper_bound":0,"sum_other_doc_count":19353,"buckets":[{"key":"Donald Morgan","doc_count":232,"books":{"doc_count":232,"avg_price":{"value":496.35258620689655}}},{"key":"Ada Wells","doc_count":228,"books":{"doc_count":228,"avg_price":{"value":473.2815789473684}}},{"key":"Barbara Hughes","doc_count":228,"books":{"doc_count":228,"avg_price":{"value":489.51842105263154}}}]},"distinct_names":{"value":128},"roles":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":"author","doc_count":20035},{"key":"translator","doc_count":6}]}}}}

doc_count: 20041 is the number of author entries (20,035 books plus 6 translators). Without reverse_nested, avg_price would not work; price is a field of the book, not of the author document. cardinality counts distinct values approximately with the HyperLogLog++ algorithm: memory use is fixed, in exchange for a small error on large numbers. Counts below the default precision_threshold (3,000) are exact in practice; 128 is the exact value (29 curated author names and 100 synthetic names, minus the author of the withdrawn Moby-Dick).

Faceted Search: a Selected Filter Must Not Break Its Own Facet

The response to the request from the guess:

{"hits":{"total":{"value":1}},"aggregations":{"categories":{"buckets":[{"key":"Fiction","doc_count":1},{"key":"Historical Fiction","doc_count":1}]},"prices":{"buckets":[{"key":"0-200","doc_count":0},{"key":"200-400","doc_count":1},{"key":"400+","doc_count":0}]}}}

Only "Fiction 1" and "Historical Fiction 1" are left in the category box. Since aggregations run on the query's result, as soon as you put the filter into the query the category facet counts only the single book in the selected category. If the user wants to switch to "Classics", they cannot see the option; they have to remove the filter first. That is not how a faceted page is expected to behave.

post_filter exists exactly for this: it is applied to the result list only, after the aggregations have been computed.

GET /books/_search
{
  "size": 0,
  "query": { "match": { "description.tr": "roman" } },
  "post_filter": { "term": { "categories": "Historical Fiction" } },
  "aggs": { "categories": { ... }, "prices": { ... } }
}
{"hits":{"total":{"value":1}},"aggregations":{"categories":{"buckets":[{"key":"Fiction","doc_count":9},{"key":"Classics","doc_count":8},{"key":"Historical Fiction","doc_count":1},{"key":"Science Fiction","doc_count":1}]},"prices":{"buckets":[{"key":"0-200","doc_count":5},{"key":"200-400","doc_count":3},{"key":"400+","doc_count":1}]}}}

The result is still one book, but the category box shows every option. Now there is the opposite problem: the price box does not see the filter either and says "0-200: 5", although there is no book in that range in the result. If the user clicks it, they get an empty page. The right rule is: every facet should be counted with all selected filters except its own. We do that by putting each facet inside a filter aggregation:

"aggs": {
  "categories": { "terms": { "field": "categories" } },
  "prices": {
    "filter": { "term": { "categories": "Historical Fiction" } },
    "aggs": { "prices": { "range": { "field": "price", "ranges": [ ... ] } } }
  }
}
{"hits":{"total":{"value":1}},"aggregations":{"categories":{"buckets":[{"key":"Fiction","doc_count":9},{"key":"Classics","doc_count":8},{"key":"Historical Fiction","doc_count":1},{"key":"Science Fiction","doc_count":1}]},"prices":{"prices":{"buckets":[{"key":"0-200","doc_count":0},{"key":"200-400","doc_count":1},{"key":"400+","doc_count":0}]}}}}

The category facet does not see its own filter and counts every option; the price facet sees the category filter and gives the right numbers. When several filters are selected, each facet's filter becomes "all other selected filters". Most search UIs call this pattern multi-select facets.

Bookstore's Facets

We put the pattern into a new BookFacets class in Bookstore.Search. Selected filters live in a dictionary keyed by facet name; the same dictionary produces both the post_filter and each facet's filter:

public static Dictionary<string, Aggregation> Aggregations(BookSearchFilter filter)
{
    Dictionary<string, Query> selected = SelectedFilters(filter);
    return new Dictionary<string, Aggregation>
    {
        [Category] = Facet(selected, Category, new Aggregation { Terms = new TermsAggregation { Field = "categories", Size = 20 } }),
        [Language] = Facet(selected, Language, new Aggregation { Terms = new TermsAggregation { Field = "language", Size = 5 } }),
        [Price] = Facet(selected, Price, new Aggregation
        {
            Range = new RangeAggregation
            {
                Field = "price",
                Ranges =
                [
                    new AggregationRange { Key = "0-200", To = 200 },
                    new AggregationRange { Key = "200-400", From = 200, To = 400 },
                    new AggregationRange { Key = "400-700", From = 400, To = 700 },
                    new AggregationRange { Key = "700+", From = 700 }
                ]
            }
        }),
        [Rating] = Facet(selected, Rating, new Aggregation
        {
            Range = new RangeAggregation
            {
                Field = "averageRating",
                Ranges = [new AggregationRange { Key = "4+", From = 4 }, new AggregationRange { Key = "3+", From = 3 }]
            }
        })
    };
}

// A facet sees every selected filter except its own, so its other options keep their counts.
private static Aggregation Facet(Dictionary<string, Query> selected, string name, Aggregation values)
{
    List<Query> others = [.. selected.Where(f => f.Key != name).Select(f => f.Value)];
    return new Aggregation
    {
        Filter = new BoolQuery { Filter = others },
        Aggregations = new Dictionary<string, Aggregation> { [Values] = values }
    };
}

The price filter now uses lt instead of lte; a user who picks maxPrice=200 should see the same set as the "0-200" facet, and in a range aggregation the upper bound is exclusive. In the endpoint, only the search text goes into the query, and the filters go to post_filter. The sorting promised in post 6 was added too; every sort ends with isbn as a tie-breaker so books with the same price do not swap places between pages:

SearchRequest<Book> request = new()
{
    From = from,
    Size = size,
    TrackTotalHits = new TrackHits(true),
    // Only the text goes into the query, so every facet counts over everything the text matched.
    Query = BookSearchQuery.Build(new BookSearchFilter(q), SearchTuning.Tuned),
    // Selected filters narrow the hits only, after the aggregations have run.
    PostFilter = BookFacets.PostFilter(filter),
    Aggregations = BookFacets.Aggregations(filter),
    Sort = BuildSort(sort),
    Highlight = string.IsNullOrWhiteSpace(q) ? null : BuildHighlight(),
    Suggest = string.IsNullOrWhiteSpace(q) ? null : BuildDidYouMean(q)
};

curl "http://localhost:5080/books/search?q=roman&category=Classics&maxPrice=200&pageSize=2"
{"total":5,"page":1,"pageSize":2,"items":[{"isbn":"9781000000214","title":"Bin Dokuz Yüz Seksen Dört","authors":["George Orwell"],"language":"tr","price":195.00,"score":9.158556,"titleHighlight":null,"descriptionHighlight":"Büyük Birader&#x27;in her şeyi izlediği bir totaliter devlette Winston Smith&#x27;in başkaldırısı; distopya <mark>romanı</mark>."},{"isbn":"9781000000078","title":"Yaprak Dökümü","authors":["Reşat Nuri Güntekin"],"language":"tr","price":175.00,"score":8.661255,"titleHighlight":null,"descriptionHighlight":"Ali Rıza Bey&#x27;in ailesinin değişen değerler karşısında dağılışını anlatan <mark>roman</mark>."}],"didYouMean":null,"facets":[{"name":"category","values":[{"key":"Classics","count":5},{"key":"Fiction","count":5},{"key":"Science Fiction","count":1}]},{"name":"language","values":[{"key":"tr","count":5}]},{"name":"price","values":[{"key":"0-200","count":5},{"key":"200-400","count":3},{"key":"400-700","count":1},{"key":"700+","count":0}]},{"name":"rating","values":[{"key":"3+","count":5},{"key":"4+","count":5}]}]}

curl "http://localhost:5080/books/search?category=Mystery&minRating=4&sort=price_desc&pageSize=2"
{"total":1362,"page":1,"pageSize":2,"items":[{"isbn":"9782000006350","title":"Window of the Wild Bridge","authors":["Ken Turner"],"language":"en","price":899.90,"score":null,...},{"isbn":"9782000169369","title":"A Window in House 58","authors":["Alan Parker"],"language":"en","price":899.90,"score":null,...}],"didYouMean":null,"facets":[{"name":"category","values":[{"key":"Science Fiction","count":1448},{"key":"History","count":1418},{"key":"Fiction","count":1413},{"key":"Travel","count":1407},{"key":"Fantasy","count":1363},{"key":"Mystery","count":1362},{"key":"Classics","count":26},{"key":"Historical Fiction","count":2},{"key":"Software","count":2},{"key":"Architecture","count":1},{"key":"Programming","count":1}]},{"name":"language","values":[{"key":"en","count":1362}]},{"name":"price","values":[{"key":"0-200","count":185},{"key":"200-400","count":336},{"key":"400-700","count":504},{"key":"700+","count":337}]},{"name":"rating","values":[{"key":"3+","count":2780},{"key":"4+","count":1362}]}]}

In the first request two filters are selected. The category facet sees the price filter (the 5 books under 200 lira), and the price facet sees the category filter and, despite its own selection, still shows the other ranges such as "200-400: 3". The second request has no search text and is sorted by price; since no score is computed, score is null, and the category facet counts books rated 4 or higher in every category.

I ran into two bugs at this step, and both are worth noting. First: with no filter selected, every search returned 503, and the log said query malformed, empty clause found. I had written PostFilter as selected.Count == 0 ? null : new BoolQuery { ... }; the expression's type was BoolQuery, so a null BoolQuery was turned into an empty Query by the client's implicit conversion and was sent as "post_filter":{}. Returning null from an explicit if fixed it. The second was a relevance bug: in post 7 we returned the "did you mean?" suggestion even when there were results, and the search "roman" suggested "road", because the suggester only looks at title terms and "roman" appears in no title. The suggestion is now returned only when there are fewer than three results.

Why Are terms Counts Approximate?

A terms aggregation picks the top shard_size values on each shard and sends them to the coordinating node, which merges them. If a value did not make it into a shard's list, its count on that shard is never added. The single-shard books index does not have this problem; to see the effect, I copied the same data into a 5-shard books-agg index with _reindex and asked for the five most frequent titles (the synthetic catalog repeats the same titles many times):

PUT /books-agg
{ "settings": { "number_of_shards": 5, "number_of_replicas": 0 }, "mappings": { "dynamic": false, "properties": { "title": { "type": "keyword" }, "categories": { "type": "keyword" } } } }

POST /_reindex?refresh=true&filter_path=total,created
{ "source": { "index": "books", "_source": ["title", "categories"] }, "dest": { "index": "books-agg" } }
{"total":20035,"created":20035}

GET /books/_search?filter_path=aggregations
{ "size": 0, "aggs": { "titles": { "terms": { "field": "title.keyword", "size": 5, "shard_size": 5 } } } }
{"aggregations":{"titles":{"doc_count_error_upper_bound":0,"sum_other_doc_count":19897,"buckets":[{"key":"The Winter Kingdom","doc_count":31},{"key":"The Little Window","doc_count":28},{"key":"The Winter Library","doc_count":27},{"key":"The Glass Station","doc_count":26},{"key":"The Golden Forest","doc_count":26}]}}}

GET /books-agg/_search?filter_path=aggregations
{ "size": 0, "aggs": { "titles": { "terms": { "field": "title", "size": 5, "shard_size": 5, "show_term_doc_count_error": true } } } }
{"aggregations":{"titles":{"doc_count_error_upper_bound":40,"sum_other_doc_count":19986,"buckets":[{"key":"The Paper Letter","doc_count":11,"doc_count_error_upper_bound":32},{"key":"The Distant City","doc_count":10,"doc_count_error_upper_bound":32},{"key":"The Hidden Letter","doc_count":10,"doc_count_error_upper_bound":32},{"key":"The Golden Season","doc_count":9,"doc_count_error_upper_bound":32},{"key":"The Little Window","doc_count":9,"doc_count_error_upper_bound":32}]}}}

# without shard_size (default: size * 1.5 + 10 = 17)
{"aggregations":{"titles":{"doc_count_error_upper_bound":34,"sum_other_doc_count":19951,"buckets":[{"key":"The Golden Forest","doc_count":22,"doc_count_error_upper_bound":14},{"key":"The Little Window","doc_count":16,"doc_count_error_upper_bound":20},{"key":"The Secret Harbour","doc_count":16,"doc_count_error_upper_bound":20},{"key":"The Little Forest","doc_count":15,"doc_count_error_upper_bound":20},{"key":"The Lost Clock","doc_count":15,"doc_count_error_upper_bound":20}]}}}

# "shard_size": 500
{"aggregations":{"titles":{"doc_count_error_upper_bound":5,"sum_other_doc_count":19897,"buckets":[{"key":"The Winter Kingdom","doc_count":31},{"key":"The Little Window","doc_count":28},{"key":"The Winter Library","doc_count":27},{"key":"The Glass Station","doc_count":26},{"key":"The Secret Mountain","doc_count":26}]}}}

On one shard the real list starts with "The Winter Kingdom 31". On five shards with shard_size: 5 the whole list is wrong: the title that really has 31 books is missing, and "The Paper Letter" is shown first with 11. The default shard_size did not change that. With 500 the order came back (apart from the tie at 26 in fifth place). doc_count_error_upper_bound is the upper bound on how much a bucket's count may be short: the sum of the largest counts the shards left out of their lists. If it is 0, the count is exact.

This example is deliberately the worst case: thousands of distinct titles with almost equal frequency. With facets the problem rarely shows up in practice; categories has 11 distinct values and language 2, and since size (20 for us) is larger than the number of values, every shard sends every value and the counts are exact. The problem appears in reports like "top 10 authors" on high-cardinality fields such as author or publisher. There, either raise shard_size and check doc_count_error_upper_bound, or use the composite aggregation, which pages through all values. We deleted books-agg afterwards.

The Same Report with ES|QL

Next to Elasticsearch's Query DSL there is also a query language written step by step: ES|QL. The same category report:

POST /_query?format=txt
{ "query": "FROM books | STATS books = COUNT(*) BY categories | SORT books DESC | LIMIT 5" }
     books     |  categories
---------------+---------------
3410           |Mystery
3385           |Travel
3341           |History
3337           |Fantasy
3320           |Science Fiction

POST /_query?format=txt
{ "query": "FROM books | WHERE MATCH(description.tr, \"roman\") | STATS books = COUNT(*), avg_price = AVG(price) BY categories | SORT books DESC" }
     books     |    avg_price    |    categories
---------------+-----------------+------------------
9              |267.6666666666667|Fiction
8              |261.125          |Classics
1              |195.0            |Science Fiction
1              |320.0            |Historical Fiction

The numbers match the terms aggregation; on the multi-valued categories field, ES|QL also creates a separate group for each value. For reporting and analysis ES|QL reads better: a developer used to SQL understands STATS ... BY immediately and gets the result as a table. For the search page we stay with the Query DSL, because it needs results sorted by relevance, highlighting, suggesters and independently filtered facets in a single request. We will call ES|QL from .NET in post 10.

Production Notes

Limit the number of facets and buckets. Every facet is computed on every search; a page with 20 facets can spend most of its search time on aggregations. Keep size in terms to what the UI actually shows.

Cache aggregations. Requests with no search text and only filters (like category pages) return the same result for every user. Requests with size: 0 also go into Elasticsearch's shard request cache; popular requests such as the first load of the search page can additionally be cached briefly in the application.

Watch doc_count_error_upper_bound. On a multi-shard index, if a report's numbers must be exact, check that this value is 0; if not, raise shard_size or walk all values with composite.

Pick price ranges from the data. Fixed ranges become unbalanced as the catalog changes; in our synthetic catalog "400-700" is the busiest range. The percentiles aggregation or a periodic report helps keep the ranges current.

Common Pitfalls

Putting the selected filter into the query. The facet loses its own options; to switch to another option, the user has to remove the filter first.

Using post_filter on its own. The selected filter is reflected in no facet, and the user sees counts that lead to empty results when clicked; every facet has to be wrapped with the other filters.

Enabling aggregation on a text field. Turning on fielddata loads every term into the heap and counts analyzed pieces ("kürk" and "mantolu" separately); the right field is the keyword subfield.

terms with a small shard_size on a high-cardinality field. On a multi-shard index it produces a wrong top 10 and the error is silent; the numbers look plausible, so nobody notices.

Counting a nested field from the root. A root-level terms on authors.name.keyword returns nothing; use nested first, and reverse_nested to get back to book fields.

A Small Experiment

★☆☆ Add a publication year facet to the endpoint (date_histogram, yearly). Which filter parameter selects it, and how is it added to SelectedFilters?

★★☆ Make the category selection multi-valued (category=Fiction&category=Classics). Within one facet the selections should be "or", across facets "and": how do you build that with a terms query? Do the category facet's counts change?

★★★ Recreate books-agg and run the "10 authors with the most books" report with a nested terms. How large does shard_size need to be before the result matches the single-shard books? Write the same report with a composite aggregation that pages through all authors and compare the results.

In the next post we take a close look at the .NET code behind these endpoints: fluent versus object initializer syntax in Elastic.Clients.Elasticsearch 9, gathering queries into a testable builder, source serializer settings, error and 429 handling, calling ES|QL from .NET, and integration tests against a real Elasticsearch with Testcontainers.