SQL Style GroupBy & OrderBy with function in ES?

Elastic Stack Elasticsearch
1

Is there a way to Order by ABS(SUM()) in ES 2.3.5?

Looks like I can only do ABS(value) as part of aggregation but not during the order part.

SQL Example

DECLARE @table TABLE
(
	Name VARCHAR(50),
	Value FLOAT    
)

INSERT INTO @table
SELECT 'First', -15.1
UNION SELECT 'Third', 7.3
UNION SELECT 'Third', -7.3
UNION SELECT 'fourth', -2.4
UNION SELECT 'Second', 12.2

SELECT TOP 3 Name, SUM(Value) [Value] FROM @table
GROUP BY Name 
ORDER BY ABS(SUM(Value)) DESC

Result

First,-15.1
Second,12.2
Fourth,-2.4

ES Example

POST /_bulk
{ "create": { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "First", "value": -15.1 }
{ "index":  { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "Third", "value": 7.3 }
{ "index":  { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "Third", "value": -7.3 }
{ "index":  { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "fourth", "value": -2.4 }
{ "index":  { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "Second", "value": 12.2 }

ES Request

GET /sorttest/_search
{
  "size": 0,
  "aggs": {
	"groupByName": {
	  "terms": {
		"field": "name",
		"size": 3,
		"order": {
		  "sumabsvalue": "desc"
		}
	  },
	  "aggs": {
		"sumvalue": {
		  "sum": {
			"field": "value"
		  }
		},
		"sumabsvalue": {
		  "sum": {
			"field": "value",
			"script": "abs(_value)"
		  }
		}
	  }
	}
  }
}

Result

First,-15.1
Third,0
Second,12.2

Any help appreciated.
M

2

It's possible to get correct ABS(SUM) values using map reduce in scripted_metric but unfortunately when trying to order by scripted_metric aggregation I get the error:

"root_cause": [
     {
        "type": "aggregation_execution_exception",
        "reason": "Invalid terms aggregation order path [customsum]. Terms buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
     }
  ]

Request:

GET /sorttest/_search
{
  "size": 0,
  "aggs": {
	"groupByName": {
	  "terms": {
		"field": "name",
		"size": 4,
		"order": {
		  "customsum": "desc"
		}
	  },
	  "aggs": {
		"sum": {
		  "sum": {
			"field": "value"
		  }
		},
		"sumabsvalue": {
		  "sum": {
			"field": "value",
			"script": "abs(_value)"
		  }
		},
		"customsum": {
		  "scripted_metric": {
			"init_script": "_agg['metric'] = []",
			"map_script": "_agg.metric.add(doc['value'].value)",
			"combine_script": "customsum = 0; for (t in _agg.metric) { customsum += t }; return abs(customsum)",
			"reduce_script": "customsum = 0; for (a in _aggs) { customsum += a }; return customsum"
		  }
		}
	  }
	}
  }
}