Date_histogram and top_hits from unique values only

Elastic Stack Elasticsearch
1

Hi,

I am trying to do a date_histogram aggregation to show a sum of Duration for each hour.

I have the following documents:
{
"EntryTimestamp": 1567029600000,
"Username": "johndoe",
"UpdateTimestamp": 1567029600000,
"Duration": 10,
"EntryID": "ASDF1234"
}

The following works very well but my problem is that sometimes multiple documents appear with the same EntryID. So ideally I would need to add a top_hits somehow, and order by the UpdateTimestamp as I need the last updated document for each unique EntryID. But not sure how to add this to my query.

{
	"size": 0,
	"query": {
		"bool": {
			"filter": [{
					"range": {
						"EntryTimestamp": {
							"gte": "1567029600000",
							"lte": "1567065599999",
							"format": "epoch_millis"
						}
					}
				}, {
					"query_string": {
						"analyze_wildcard": true,
						"query": "Username.keyword=johndoe"
					}
				}
			]
		}
	},
	"aggs": {
		"2": {
			"date_histogram": {
				"interval": "1h",
				"field": "EntryTimestamp",
				"min_doc_count": 0,
				"extended_bounds": {
					"min": "1567029600000",
					"max": "1567065599999"
				},
				"format": "epoch_millis"
			},
		    "aggs": {
			    "1": {
				    "sum": {
					    "field": "Duration"
			    	}
			    }
		    }
		}
	}
}
2

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