elastic / elastic/elasticsearch
ESQL: TOPK
- Dominant language
- Java
- Stars
- 77.9k
- Forks
- 26.1k
- PR merge metrics
- PR metrics pending
Description
PromQL's `topk` / `bottomk` ranking functions exist in Elasticsearch (see `PromqlBuiltinFunctionDefinitions`), but there is no ES|QL equivalent that preserves full rows. The closest options today fall short:
- `STATS … | TOP(field, k)` packs the winning values into a multivalue array, losing the rest of each row.
- `SORT field DESC | LIMIT k BY group` gives the right rows, but it is verbose, splits one intent across two commands, and (unlike PromQL) does not read as ranking.
### Proposal
Add row-preserving ranking commands:
```esql
FROM k8s
| TOPK network.bytes_in, 3 BY pod -- 3 highest rows per pod
| BOTTOMK network.bytes_in, 3 BY pod -- 3 lowest rows per pod
| LIMITK 3 BY pod -- arbitrary 3 rows per pod
```
The `BY` clause is optional; without it the commands apply globally. `k` must be a non-negative integer, like `LIMIT`.
Example:
```esql
FROM employees
| TOPK salary, 1 BY gender
| KEEP first_name, last_name, salary, gender
| SORT gender
```
| first_name | last_name | salary | gender |
|---|---|---|---|
| Tzvetan | Zielinski | 74572 | F |
| Otmar | Herbst | 74999 | M |
| Lillian | Haddadi | 73717 | null |
### Implementation sketch
The commands desugar in the parser to exactly what the equivalent `SORT | LIMIT [BY]` parses to (`OrderBy` + `LimitBy`/`Limit`), so analysis, optimization (`ReplaceLimitAndSortAsTopN` folds them into `TopNBy`/`TopN`), and distributed execution are reused with no new execution code. New `TOPK`/`BOTTOMK`/`LIMITK` lexer keywords plus a gating capability (e.g. `ESQL_TOPK_COMMAND`).
### Status
- Supersedes the `STATS`-aggregate approach in #159219 (closed): aggregates collapse each group into a multivalue array and cannot preserve rows, so they don't match PromQL ranking semantics.
- Draft implementation: #159228.
### Open questions
- Syntax/naming: is `TOPK , [BY ]` the right surface?
- `LIMITK k [BY …]` is a pure alias of `LIMIT k [BY …]` — keep for PromQL-family symmetry or drop?
- Telemetry currently reports the desugared `LIMIT BY`/`SORT` nodes rather than `TOPK`; acceptable or worth a dedicated label?
Contributor guide
Assessment
This issue has not been assessed yet.