[ES|QL][Kibana request] Return _id by default

[stratoula]

Description

In #108305 we want to have a way to return by default timeseries sorted by a given timefield. This is something that I am expecting to work like that across kibana as almost all applications want to render their data sorted by time (Lens, Dashboards, Solutions, Discover)

There is an extra request for Discover though. We would like to return by default the _id of the document. Atm we have it with the metadata _id function but this is not very user friendly. We want the user to type from logs and get the table + an extra column, the _id. This is important for Discover as we can use this id to retrieve more information. For example:

• The user clicks a row, a flyout with the row details appears and at this point I want to run an ES|QL query in the background to retrieve more metadata (or run statistics etc) only for this row. I dont want to do this by default as it is not performant. I want to run these extra queries on user's demand.

This should not change the current behavior of ES|QL. I am expecting to be a Discover mode where we can get a little bit different defaults. I don't expect that this meta information will be important everywhere in Kibana (for example we don't need the id in a visualization). So I would like to differentiate it from the sort by timestamp request

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[stratoula]

We want to use this _id to do an extra call (after user's request) to get this row. Is the _id enough or do we need the _index too?

from <source> | where _id === <value>

Also is this performant?

[bpintea]

Is the _id enough or do we need the _index too?
from <source> | where _id === <value>

The _id is index specific, so you'll probably want the _index too.

(Smth like PUT test1/_doc/1 and PUT test2/_doc/1 will get you the same _id==1 for a FROM test*.)

Also is this performant?

I think the loading of the fields should be OK. Filtering-wise we can push _index down, but we don't do it for_id. But maybe we can improve things if required.

[stratoula]

Thanx @bpintea ❤️

[nik9000]

_id is a stored field so it's not the most efficient thing to load, but we will only be loading it outside of stats so that'll help. We'd want to make sure we did it after a topn.

[sphilipse]

I want to ➕ 1️⃣ this request, particularly having the _index and _id populated by default would be really useful for Search use cases where we don't usually care about timestamps as much but do really care about where a document came from in Discover.

[nik9000]

Since you can get these with an explicit syntax I wonder if we can cover this with default values in the box depending on your solution. I'm not really sure whether than makes any sense though. Another thing: users can write queries so they can remove the _id and _index. Or remap it. It's all valid esql. Do you want some way to prevent that? Like an option that says "If the user tries to modify these fields reject the query."

…

On Wed, May 15, 2024, 9:16 AM Sander Philipse ***@***.***> wrote: I want to ➕ 1️⃣ this request, particularly having the _index and _id populated by default would be really useful for Search use cases where we don't usually care about timestamps as much but do really care about where a document came from in Discover. — Reply to this email directly, view it on GitHub <#108398 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABUXIVPHXCFHIG46O45G6DZCNNZHAVCNFSM6AAAAABHMNDWCKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJSGQ4TANJXHE> . You are receiving this because you are on a team that was mentioned.Message ID: ***@***.***>

[stratoula]

Good point, I was hoping that they won't 😂 It makes sense to not allow them to modify them otherwise we wont be able to do the stuff we want

[stratoula]

I am closing this as we have managed to walk around it and it doesnt seem to be necessary for now. We can always reopen and re-discuss if the need comes