ES|QL: support nested fields

[thomasneirynck]

Description

ES|QL should support nested-fields.

---

Consider something along the lines of:

• A command to unpack a nested fields into rows.

e.g.: FROM foobar | EXPAND my_nested_field by_path "pathtonestedfield | ...

This would yield a denormalized table, something like

foobar1-fieldX, foobar1-fieldY, ..., childa
foobar1-fieldX, foobar1-fieldY, ..., childb
foobar2-fieldX, foobar2-fieldY, ..., childa
foobar2-fieldX, foobar2-fieldY, ..., childb
foobar2-fieldX, foobar2-fieldY, ..., childc
...

• New/overloaded aggregations that support a path-parameter

e.g.

FROM foobar | STAT avg_nested(my_nested_field, "child_path") BY ... 

• ... or any other number of approaches

  ---

A downstream benefit is that nested-field support in ES|QL would immediately enable visualization support for them in Kibana (https://github.com/elastic/kibana/issues/1084)

[thomasneirynck]

This is similar to https://github.com/elastic/elasticsearch/issues/105637

[elasticsearchmachine]

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

[jgb]

Just to voice my support in favor of this feature. I'm a long time user of elasticsearch, and would love to start exploring ES|SQL, but not supporting nested fields is a showstopper at the moment for most of my data.

[syepes]

+1