elastic / elastic/elasticsearch

ESQL: TOPK

Open
#159,304 1 comment 0 reactions 1 assignee Claimed by @sidosera View on GitHub
:StorageEngine/ES|QL >feature Team:StorageEngine
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.