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.

[terrancedejesus]

The few security integrations such as Okta, AWS and Azure I have used have flattened fields that have very contextual data in them for writing detection rules. At the very least, would a simple temporary solution be to allow to_string() on any flattened fields? This way we can dissect or grok as need be. The challenge for many from what I have read is that the data is completely inaccessible with ES|QL.

[terrancedejesus]

Adding an update.

We are running into blockers with ES|QL for detection rules and threat hunting in the security solution. An example is the Okta integration where okta.target field is a flattened field only. There is no other field type for this and as such, we are unable to leverage all the functionality of ES|QL on this field. This field contains important information about the target IAM or IdP that we want to either process with GROK or DISSECT or filter on.

A security integrations issue was created to hopefully create a keyword version of okta.target for the time-being, allowing us to at least use GROK or DISSECT, however there appears to be additional considerations that security integrations have brought to our attention.

Ref: elastic/integrations#11049 (comment)

[ty-elastic]

I'll add that the EDOT (Elastic Distro of Open Telemetry) Collector's Elasticsearch Exporter uses the 'flattened' datatype for some types of native OTel logs (structured logs, specifically)... Which means that the interesting ("message") part of the log entry is inside a flattened datatype, and thus non-queryable via ES|QL.

[wchaparro]

@tylerperk @quackaplop FYI