Skip to content

Support extra output field in TOP function#135434

Merged
przemekwitek merged 26 commits intoelastic:mainfrom
przemekwitek:top_sortField
Nov 13, 2025
Merged

Support extra output field in TOP function#135434
przemekwitek merged 26 commits intoelastic:mainfrom
przemekwitek:top_sortField

Conversation

@przemekwitek
Copy link
Copy Markdown

@przemekwitek przemekwitek commented Sep 25, 2025 •
edited
Loading

There are cases where we would like to answer queries like:
"give me list of employees sorted by their salaries"
"give me list of countries sorted by their area"
etc.
Currently, the TOP aggregation function only supports one field which is used as both sort field and the output field.
This PR enhances the TOP function by adding an optional parameter mapToField which is the field we want as the output. The sorting will still be performed on the field, just like today.

Example:
"give me salaries of 3 youngest employees by gender"

FROM employees
| STATS youngest_employees = TOP(birth_date, 3, "desc"),
        youngest_employees_salaries = TOP(birth_date, 3, "desc", salary)
  BY    gender
| SORT gender
| KEEP gender, youngest_employees, youngest_employees_salaries
;

gender:keyword | youngest_employees:datetime                                                    | youngest_employees_salaries:integer
F              | [1964-10-18T00:00:00.000Z, 1964-06-02T00:00:00.000Z, 1963-03-21T00:00:00.000Z] | [25976, 56371, 43602]
M              | [1965-01-03T00:00:00.000Z, 1964-06-11T00:00:00.000Z, 1964-04-18T00:00:00.000Z] | [37702, 45656, 46595]
null           | [1963-06-07T00:00:00.000Z, 1963-06-01T00:00:00.000Z, 1961-05-02T00:00:00.000Z] | [48735, 45797, 61358]
;

Fixes #128630

@przemekwitek przemekwitek force-pushed the top_sortField branch 4 times, most recently from ebada8e to d7003ba Compare October 6, 2025 08:05
@github-actions
Copy link
Copy Markdown
Contributor

github-actions bot commented Oct 6, 2025

ℹ️ Important: Docs version tagging

👋 Thanks for updating the docs! Just a friendly reminder that our docs are now cumulative. This means all 9.x versions are documented on the same page and published off of the main branch, instead of creating separate pages for each minor version.

We use applies_to tags to mark version-specific features and changes.

Expand for a quick overview

When to use applies_to tags:

✅ At the page level to indicate which products/deployments the content applies to (mandatory)
✅ When features change state (e.g. preview, ga) in a specific version
✅ When availability differs across deployments and environments

What NOT to do:

❌ Don't remove or replace information that applies to an older version
❌ Don't add new information that applies to a specific version without an applies_to tag
❌ Don't forget that applies_to tags can be used at the page, section, and inline level

🤔 Need help?

Przemyslaw Witek and others added 24 commits November 13, 2025 09:53
Apply review comments
085ebda
- Add enhanced TOP tests to TopTests.java
761d822 
- Fix grow() method in X-BucketedSort
- Add `date` type to supported types of `outputField`
Fix style with spotless
8d0ce1b
Fix TOP's type resolution and add appropriate tests
f3a05bc
Regenerate TopXYAggregatorFunction classes.
776dd42
Add tests for new surrogates
27f4845
Apply spotless
1348bb8
Fix stats_top.empNoByHireDateByBirthYear by fixing the order of rows
2c00535
Apply review comment
ea29886
[CI] Auto commit changes from spotless
6044a23
Add "begin/end generated imports" directives to X-BucketedSort.java.s…
b9b9f04 
…t file
Make spotless happy
53d7b27
Apply review comments
5aa60dc
spotless
701d87f
Include description in appliesTo
b76b8ae
Add generated appliesTo description
012bcf2
Revert surrogate unit tests
779e8b2
Adapt Top.java after merge
7208add
Add missing multi-match description
893bd6e
Revert FunctionAppliesTo changes.
7988b24 
Put inline applies_to in the outputField param section.
Fix style
408329a
Enhance description
1f446c1
Fix the docs typo.
a8dca09
Fix typo in proper file.
37ad6b7
@przemekwitek przemekwitek merged commit 938256e into elastic:main Nov 13, 2025
34 checks passed
@przemekwitek przemekwitek deleted the top_sortField branch November 13, 2025 11:13
@nicpenning
Copy link
Copy Markdown
Contributor

nicpenning commented Nov 13, 2025 •
edited
Loading

How does this relate to #108385

Are they similar functionalities?

My use case was documented here: #128612

Perhaps this might be a similar way to get my desired result?

szybia added a commit to szybia/elasticsearch that referenced this pull request Nov 13, 2025
@szybia
Merge remote-tracking branch 'upstream/main' into ilm-explain-invalid…
fea0974 
…-json

* upstream/main: (158 commits)
  Cleanup files from repo root folder (elastic#138030)
  Implement OpenShift AI integration for chat completion, embeddings, and reranking (elastic#136624)
  Optimize AsyncSearchErrorTraceIT to avoid failures (elastic#137716)
  Removes support for null TransportService in RemoteClusterService (elastic#137939)
  Mute org.elasticsearch.index.mapper.DateFieldMapperTests testSortShortcuts elastic#138018
  rest-api-spec: fix type of enums (elastic#137521)
  Update Gradle wrapper to 9.2.0 (elastic#136155)
  Add RCS Strong Verification Documentation (elastic#137822)
  Use docvalue skippers on dimension fields (elastic#137029)
  Introduce INDEX_SHARD_COUNT_FORMAT (elastic#137210)
  Mute org.elasticsearch.xpack.inference.integration.AuthorizationTaskExecutorIT testCreatesChatCompletion_AndThenCreatesTextEmbedding elastic#138012
  Fix ES|QL search context creation to use correct results type (elastic#137994)
  Improve Snapshot Logging (elastic#137470)
  Support extra output field in TOP function (elastic#135434)
  Remove NumericDoubleValues class (elastic#137884)
  [ML] Fix ML calendar event update scalability issues (elastic#136886)
  Task may be unregistered outside of the trace context in exceptional cases. (elastic#137865)
  Refine workaround for S3 repo analysis known issue (elastic#138000)
  Additional DEBUG logging on authc failures (elastic#137941)
  Cleanup index resolution (elastic#137867)
  ...
@km1967
Copy link
Copy Markdown

km1967 commented Dec 28, 2025

The discussion on

"Are we excluding keyword/bytesrefs-related types temporarily to be added later...?"

was answered (I thought) with an effective Yes, excluded just temporarily, and the other combinations would be added later.

"But eventually, we should support more combinations (potentially 7x7=49 rather than current 4x4=16)."

Is this still the plan? Particularly where OutputField is keyword.

"give me list of employee names, sorted by their salaries" !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@florent-leborgne florent-leborgne florent-leborgne left review comments

@leemthompo leemthompo leemthompo left review comments

@ivancea ivancea ivancea approved these changes

Assignees

No one assigned

Labels

:Analytics/ES|QL AKA ESQL >enhancement Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo) v9.3.0

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

[ES|QL] TOP Allow ordering by a different field

7 participants

@przemekwitek @elasticsearchmachine @nicpenning @km1967 @ivancea @florent-leborgne @leemthompo