How do you sort and page Elasticsearch aggregation buckets by a computed value?

## The two pieces **`bucket_script`** is a parent pipeline that gives each bucket a new numeric value computed from other values in that bucket. It takes `buckets_path` as a map of variable names to paths and a Painless `script` that reads them as `params`: ```json "return_rate": { "bucket_script": { "buckets_path": { "o": "orders", "r": "returns" }, "script": "params.o == 0 ? 0 : params.r / params.o" } } ``` Guarding against a zero denominator matters: an unguarded division produces infinity or a null and then sorts to a corner of the result. **`bucket_sort`** is a parent pipeline that reorders — and optionally truncates — the bucket list of the aggregation it sits in. It takes a `sort` array whose entries are paths to bucket values (a metric, a `bucket_script` output, `_key`, `_count`), each with an `order`, plus `from`, `size` and `gap_policy`. ## Why terms `order` is not enough A `terms` aggregation can order its buckets by `_count`, by `_key`, or by a single-value metric sub-aggregation. It **cannot** order by a pipeline aggregation. The reason is structural: `terms` selects and orders its buckets while the aggregation is being computed and merged, whereas pipeline aggregations run afterwards over the finished result. A value that does not exist yet cannot drive the selection that produces it. `bucket_sort` closes the loop by re-sorting after the fact. ## Ordering versus selecting: the correctness caveat This is the part interviewers are really testing. `bucket_sort` **re-sorts what it was given**. The pipeline runs after the source aggregation has already decided which buckets exist. With a `terms` aggregation, that decision was made by the aggregation's own `order` and `size`: 1. Each shard returns its top `shard_size` terms by the terms ordering. 2. The coordinating node merges and keeps the top `size`. 3. `bucket_script` computes the rate for those buckets. 4. `bucket_sort` ranks those buckets by the rate. A seller with a catastrophic return rate and a modest order count never enters at step 1 or 2, so no amount of sorting at step 4 finds them. The honest phrasing for a report is "the worst return rate among the 500 busiest sellers", and if the business needs the true worst, the request must change: raise `size` and `shard_size` until the candidate set plausibly contains the answer (paying memory and reduce cost), order the `terms` aggregation by a metric correlated with the target where one exists, or enumerate every bucket with a paging aggregation such as `composite` and rank outside Elasticsearch. ## from and size `bucket_sort` accepts `from` and `size`, which window the sorted bucket list. This looks like paging and is useful for serving page 2 of a table, but it is not deep paging: every page still requires the source aggregation to build the same candidate set, and moving `from` deeper does not reach buckets outside it. For genuine enumeration of a large bucket space, a paging bucket aggregation is the right tool. Used with `size` alone and no `sort`, `bucket_sort` is a pure truncation — a compact way to say "keep the first N buckets of this histogram" without changing their order. That idiom is common for trimming long time series before returning them. ## Cost and placement Both aggregations are parent pipelines and must be declared inside the multi-bucket aggregation whose buckets they operate on. Both run in the reduce phase on the coordinating node, so their cost is per bucket, not per document: script compilation once, then a cheap evaluation per bucket, then a sort. On a bucket set of a few hundred this is negligible; on tens of thousands of buckets across many script stages it becomes real coordinating-node CPU, which is a reason to keep bucket counts bounded rather than to avoid the pipelines. ## Putting it together A typical "worst offenders" panel is: a `terms` aggregation with a deliberately generous `size`, two `sum` metrics inside it, a `bucket_script` turning them into a ratio, optionally a `bucket_selector` dropping buckets with too few documents to be meaningful, and a `bucket_sort` ranking by the ratio and keeping the top ten. Every stage runs after the previous one on the merged result, which is why the whole chain inherits the candidate-set limitation of the very first step.