# Aggregation on most recent document in a group

**URL:** <https://discuss.elastic.co/t/aggregation-on-most-recent-document-in-a-group/129335>\
**Category:** Elasticsearch\
**Created:** [April 24, 2018, 3:17pm UTC](https://discuss.elastic.co/t/aggregation-on-most-recent-document-in-a-group/129335 "2018-04-24T15:17:12Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![lasitha1991](https://avatars.discourse-cdn.com/v4/letter/l/6de8d8/32.png) [@lasitha1991](https://discuss.elastic.co/u/lasitha1991)\
**Post date:** [April 24, 2018, 3:17pm UTC](https://discuss.elastic.co/t/aggregation-on-most-recent-document-in-a-group/129335/1 "2018-04-24T15:17:14Z")

</div>

Hi All,

I have a dataset as follows  
{  
"unit\_id":150,  
"value":20,  
"timestamp":"2018-04-24 11:00AM"  
}

{  
"unit\_id":150,  
"value":15,  
"timestamp":"2018-04-24 11:30AM"  
}

{  
"unit\_id":151,  
"value":10,  
"timestamp":"2018-04-24 11:15AM"  
}

{  
"unit\_id":151,  
"value":5,  
"timestamp":"2018-04-24 11:35AM"  
}

I want to write a elasticsearch query to aggregate the sum of "value" field, from the latest records for each unit\_id. (i.e. result value should be 15+5=20)

Highly appreciate any help on this.

Thank you very much

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [April 24, 2018, 3:40pm UTC](https://discuss.elastic.co/t/aggregation-on-most-recent-document-in-a-group/129335/2 "2018-04-24T15:40:21Z")

</div>

What's the number of unique unit\_ids?

---

<div class="post-metadata">

**Author:** ![lasitha1991](https://avatars.discourse-cdn.com/v4/letter/l/6de8d8/32.png) [@lasitha1991](https://discuss.elastic.co/u/lasitha1991)\
**Post date:** [April 24, 2018, 3:50pm UTC](https://discuss.elastic.co/t/aggregation-on-most-recent-document-in-a-group/129335/3 "2018-04-24T15:50:40Z")

</div>

it will be around 4000

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [April 24, 2018, 4:43pm UTC](https://discuss.elastic.co/t/aggregation-on-most-recent-document-in-a-group/129335/4 "2018-04-24T16:43:47Z")

</div>

> [@lasitha1991](#):
>
> it will be around 4000

In which case this ugliness may be viable:

```
POST test/_search?size=0
{

	"aggs": {
		"sum": {
			"scripted_metric": {
				"init_script" : "params._agg.transactions = new HashMap()",
				"map_script" : """
					// Gather latest date and value for each unique unit_id
					def d =params._agg.transactions.get(doc.unit_id.value);
					if(d==null) {
						d=['value':doc.value.value, 'timestamp': doc.timestamp.value];
						params._agg.transactions.put(doc.unit_id.value, d);
					} else {
					  if(doc.timestamp.value.getMillis() > d.timestamp.getMillis()){
						d.value = doc.value.value;
						d.timestamp = doc.timestamp.value;
					  }
					}
					""", 
				"combine_script" : "return params._agg.transactions",
				"reduce_script" : """
				  def reduced = new HashMap();
				  // Take only latest date and value from shard results
				  for (agg in params._aggs){
					  for(unit_id in agg.keySet()){
						def unit_details =agg.get(unit_id);
						def reduced_unit_details =reduced.get(unit_id);
						if(reduced_unit_details==null) {
							reduced.put(unit_id, unit_details);
						} else {
						  if(unit_details.timestamp.getMillis() > reduced_unit_details.timestamp.getMillis()){
							reduced.put(unit_id, unit_details);
						  }
						}
					  }
				  }
			  
				  // Sum the latest reported values for each unit
				  def result =0;
				  for(unit_details in reduced.values()){
					result+=unit_details.value;
				  }
				  return result;
				"""
			}
		}
	}
}

```

---

<div class="post-metadata">

**Author:** ![lasitha1991](https://avatars.discourse-cdn.com/v4/letter/l/6de8d8/32.png) [@lasitha1991](https://discuss.elastic.co/u/lasitha1991)\
**Post date:** [April 24, 2018, 4:47pm UTC](https://discuss.elastic.co/t/aggregation-on-most-recent-document-in-a-group/129335/5 "2018-04-24T16:47:10Z")

</div>

Many Thanks Mark,, I'll try this out.

---

<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:** [May 22, 2018, 4:47pm UTC](https://discuss.elastic.co/t/aggregation-on-most-recent-document-in-a-group/129335/6 "2018-05-22T16:47:13Z")

</div>

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