ESQL should support the "flattened" field type

[jamshid]

Elasticsearch Version

8.12.1

Installed Plugins

No response

Java Version

bundled

OS Version

rockylinux:a

Problem Description

As mentioned in https://www.elastic.co/guide/en/elasticsearch/reference/8.12/esql-limitations.html#_unsupported_types the flattened field type is not supported by ESQL. I don't find another issue tracking that support. Is it planned and any idea when, like in 8.x or not until 9?

Steps to Reproduce

Try to use ESQL to aggregate on a flattened field.

Logs (if relevant)

No response

[elasticsearchmachine]

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

[nik9000]

I don't have an estimate. One of the fun parts about flattened is that we don't know what the actual sub-fields are at planning time.

[lizozom]

Hey @nik9000, I noticed that I can't even fetch a flattened field using ESQL.
It shows up as empry.
Is there any reason not to support this at least?

[nik9000]

Is there any reason not to support this at least?

That fetch is sort of step 0 in supporting flattened. I can only think of two reasons we're not working on it now though - we're busy with other stuff, and, maybe worse, flattened fields are very hard to resolve into columns which is something that ESQL needs to do up front. That's not really possible for flattened fields so we'd need to come up with an interesting way to make them visible. More than any other field type they need a "design".

The tricky thing is that flattened fields fundamentally don't know the names of their sub-fields. That's the point of them - we don't want any per-sub-field overhead. ESQL could load any particular sub-field of a flattened field similarly to a keyword field if you knew the name up front. I imagine we could push queries to it and all of that too.

But flattened fields need some way to load all of the sub-fields. Maybe some kind of object response format or something. But I'm not sure.

[BenB196]

I'd also be curious on how Elastic is prioritizing this feature (understanding that it is a complex topic). flattened fields always seem to be somewhat of the odd one out (right in front of nested fields). One of my biggest frustrations with Elastic recently has been with the disconnect around flattened fields adoption and product support.

On one hand you have the Elastic integration teams widely adopting flattened fields, ~106 integrations using them, but on the other hand you have other core Elastic products (EQL #101985, ES|QL, Kibana elastic/kibana#25820) that seem to stay far away from supporting them. This makes a significant portion of valuable data in Elasticsearch hard to use.

I was hoping that ES|QL would cover this, elastic/kibana#25820 (comment), but as pointed out in that issue, and by this issue's existence, this gap in support has largely remained open and frustrating to deal with.