ESQL: Ability to aggregate on _tier metadata

[klacabane]

Description

Terms aggregation on _tier metadata field results in an error. It is not clear whether this is intended behavior or a bug, but I'd expect this to work since a similar aggregation on _index metadata succeeds.

Use case

The kibana streams plugin shows a graph displaying document counts per tier for data streams managed by an ILM policy.
We currently query the document count per _index and cross that data with _ilm/explain. Aggregating on _tier would simplify the approach.

Issue

POST logs/_search
{
  "track_total_hits": false,
  "size": 0,
  "aggs": {
    "tiers": {
      "terms": {
        "field": "_tier"
      }
    }
  }
}

--->
...
{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "Fielddata is not supported on field [_tier] of type [_tier]"
      }
    ],
...
}

[elasticsearchmachine]

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

[wchaparro]

Lets move this to ESQL, We won't work on this for legacy Aggregations.