Aggregation on most recent document in a group

Elastic Stack Elasticsearch
1

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

2

What's the number of unique unit_ids?

3

it will be around 4000

4

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;
				"""
			}
		}
	}
}
5

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

6

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