[Lens] support both sorting and array values on last value dimensions

[drewdaemon]

Describe the feature: When users are dealing with a Last value dimension, they currently have the choice between displaying array values and allowing the dimension to be used as the sort-by for a top values dimension.

Not supporting array values

Supporting array values

While it wasn't the original reason we added this, the tradeoff actually makes sense because it is not clear how sorting by array values should work. It could

• use the first value of the array
• apply a function over the array (max, median, etc.)
• many other options

However, the behavior when not supporting array values is currently not ideal. The displayed values are computed from each array based on an undefined Elasticsearch operation. When users attempt to filter by these values, they see strange behavior since the filtered values don't actually exist in the data.

Instead of providing the tradeoff switch, we should support sorting by array values by making the sorting behavior explicit.

• Remove the switch
• Display how the array values are being used to sort the terms
• Allow the user to change which strategy is used

This will allow us to both order top values by a last value dimension and display correct values

[elasticmachine]

Pinging @elastic/kibana-visualizations (Team:Visualizations)

[ppisljar]

if i understand the issue correctly, the problem with top hits is that it does not support sorting ? one way to work around that would be to keep using top hits, then create a runtime field (on-the-fly) that reduces the multivalue field into a single value (average, sum, min, max, count of values. ...) and then use that field for sorting.

i am guessing es does a similar thing behind the scenes in top metrics agg (reduces the multivalue field using average) so it can sort on that value.

in lens this could happen completely transparent to the user.