SQL: improve handling of ES aggregations

[costin]

Currently underlying ES aggregations are returned as are, both as sub-aggregations and bucket selector.
Unfortunately due to the underlying inconsistencies this can have undesirable effects, for example:

1. MAX/MIN can work on date fields however they return an actual long instead of a time object causing failures
2. aggregations on null/empty groups return their initial result, not null which again is problematic (see Introduce contract to know whether InternalAggs have computed results or not #34903).

In both example, this can be handled inside SQL by wrapping the aggregation to guarantee the expected value.

[elasticmachine]

Pinging @elastic/es-search-aggs

[polyfractal]

For 1), you can set the format property of min/max aggregations to format the long into a date. E.g.

GET /test/_search
{
  "size": 0,
  "aggs": {
    "the_max": {
      "max": {
        "field": "timestamp",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

Returns:

{
  "took": 5,
  "timed_out": false,
  "_shards": {...},
  "hits": {...},
  "aggregations": {
    "the_max": {
      "value": 1542756678236,
      "value_as_string": "2018-11-20"
    }
  }
}

If you're working with the java objects, you should be able to set the format in the request, and then from the NumericMetricsAggregation.SingleValue object you can use getValueAsString(). It unfortunately looks like getValueAsString() is not part of the Max or Min interface at the moment, but I think this is probably an oversight instead of purposeful.

EDIT: Oh, if you were looking for the time object instead of the string then you can safely ignore everything I just said :)

[costin]

@astefan can you please take a look at incorporating this into MetricAggExtractor? Thanks.

[astefan]

Second part of this issue has been implemented via #44745.

[elasticsearchmachine]

Pinging @elastic/es-ql (Team:QL)

[elasticsearchmachine]

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

[wchaparro]

Superceded by ES|QL