search

elastic / elasticsearch

Free and Open Source, Distributed, RESTful Search Engine
https://www.elastic.co/products/elasticsearch
Other
69.56k stars 24.62k forks source link

ESQL should support the "flattened" field type #105637

Open jamshid opened 7 months ago

jamshid commented 7 months ago

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 commented 7 months ago

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

nik9000 commented 6 months ago

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 commented 6 months ago

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? image

nik9000 commented 6 months ago

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 commented 6 months ago

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 https://github.com/elastic/elasticsearch/issues/101985, ES|QL, Kibana https://github.com/elastic/kibana/issues/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, https://github.com/elastic/kibana/issues/25820#issuecomment-1803659184, 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 commented 1 week ago

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.