# Scripted metric aggregations & sorting

**URL:** <https://discuss.elastic.co/t/scripted-metric-aggregations-sorting/143719>\
**Category:** Elasticsearch\
**Created:** [August 9, 2018, 2:20pm UTC](https://discuss.elastic.co/t/scripted-metric-aggregations-sorting/143719 "2018-08-09T14:20:58Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![cohenran](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cohenran/32/29697_2.png) [@cohenran](https://discuss.elastic.co/u/cohenran)\
**Post date:** [August 9, 2018, 2:20pm UTC](https://discuss.elastic.co/t/scripted-metric-aggregations-sorting/143719/1 "2018-08-09T14:20:58Z")

</div>

I tried to select by a scripted\_metric aggregations & sort it by timestamp field.

The result seems to be divided into sorted arrays in the aggregation bucket.  
However, the arrays themselves are not sorted between themselves (see the screenshot).

1. Why are there many sub-arrays in the result?
2. Why the sub-arrays not sorted between themselves?

**Query:**  
"aggregations": {  
"ccpairTerm": {  
"terms": {  
"field": "ccpair"  
},  
"aggregations": {  
"timestampTerm": {  
"scripted\_metric": {  
"init\_script": {  
"source": "params.\_agg.lpSendingTime=;params.\_agg.ccpairs=;params.\_agg.tnetServerNames=;params.\_agg.platformNames=",  
"lang": "painless"  
},  
"map\_script": {  
"source": "params.\_agg.lpSendingTime.add(doc.lpSendingTime.value);params.\_agg.ccpairs.add(doc.ccpair.value);params.\_agg.tnetServerNames.add(doc.tnetServerName.value);params.\_agg.platformNames.add(doc.platformName.value)",  
"lang": "painless"  
},  
"combine\_script": {  
"source": """  
List result;  
result = ;  
params.\_agg.lpSendingTime.sort((x, y) -\> (int)(x.getMillis() - y.getMillis()));  
for (int i = 0 ; i \< params.\_agg.lpSendingTime.length-1 ; i++)  
{  
if (params.\_agg.lpSendingTime[i + 1].getMillis() - params.\_agg.lpSendingTime[i].getMillis() \> 10) {  
result.add(params.\_agg.lpSendingTime[i].getMillis())  
}  
} return result;  
""",  
"lang": "painless"  
}  
}  
}  
}  
}

**Result:**  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/a/2ac63322e27df608606a75943c9636563a7dab71.png)

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [August 10, 2018, 12:03pm UTC](https://discuss.elastic.co/t/scripted-metric-aggregations-sorting/143719/2 "2018-08-10T12:03:14Z")

</div>

you have not defined a `reduce_script` in your scripted\_metric aggregation above so what you are seeing is the raw result of the `combine_script` from each shard. You need to define a `reduce_script` which takes the result from each shard and merges them together into your final result. See the following documentation for more information: [https://www.elastic.co/guide/en/elasticsearch/reference/6.3/search-aggregations-metrics-scripted-metric-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/6.3/search-aggregations-metrics-scripted-metric-aggregation.html)

Also I wonder what your use case for using the scripted\_metric aggregation is here? Often there are ways of achieving what you need without using the scripted\_metric aggregation and instead combining the `script` feature in another aggregation instead

---

<div class="post-metadata">

**Author:** ![cohenran](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cohenran/32/29697_2.png) [@cohenran](https://discuss.elastic.co/u/cohenran)\
**Post date:** [August 12, 2018, 8:01am UTC](https://discuss.elastic.co/t/scripted-metric-aggregations-sorting/143719/3 "2018-08-12T08:01:04Z")

</div>

Thanks for the fast reply!

The data is a collection of timestamps.  
I want to know if there is a gap between 2 consecutive timestamps (index split to more than one shard - 2 consecutive timestamps can on different shards).

1. Is it possible to gather all data and sort it after?
2. How can we do it with a script?
3. How can we do it without script (Query only)?

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [August 13, 2018, 8:48am UTC](https://discuss.elastic.co/t/scripted-metric-aggregations-sorting/143719/4 "2018-08-13T08:48:09Z")

</div>

> [@cohenran](#):
>
> Is it possible to gather all data and sort it after?

If you wanted to do the analytics offline you could use the scroll API to stream all the data out of Elasticsearch and do your calculations on your client.

> [@cohenran](#):
>
> - How can we do it with a script?
> - How can we do it without script (Query only)

The problem with your approach here is that you are potentially going to need to stream a lot of data from the shards to the coordinating node because the number of timestamps could be large. You could mitigate this in two ways:

1. When indexing documents use `routing` to route documents with the same `ccPairs` value to the same shard - This way you are guaranteed that all timestamps for a term bucket are on the same shard. You will still need to do complex processing though so will likely still need to use the scripted\_metric aggregation.
2. Have a secondary index where each document represents a `ccPairs` value and contains the information about whether there is a gap and at what timestamps - This involves having a job that runs periodically, collects new data from the primary index and merges that new data into the relevant documents int eh secondary index. At query time you can than run normal aggregations to obtain the information you need if you structure the documents in the secondary index in appropriate ways to show this data.

If your data volumes are small enough that your current approach (after you add a reduce script) seems to be working well then you can continue with this approach but it might be worth keeping the above in mind if your data volume increases and performance starts to suffer.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 10, 2018, 8:48am UTC](https://discuss.elastic.co/t/scripted-metric-aggregations-sorting/143719/5 "2018-09-10T08:48:16Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.