I have a document per app instance per cloud. For example:
cloud: 1, appInstanceId: 123, timeReceived: 1509371566235291000 (timestamp stored as long), numWidgets: 99
cloud: 1, appInstanceId: 802, timeReceived: 1509371566235291000, numWidgets: 45
cloud: 1, appInstanceId: 123, timeReceived: 1509371571672334000, numWidgets: 102
cloud: 2, appInstanceId: 267, timeReceived: 1509371566235291000, numWidgets: 88
cloud: 2, appInstanceId: 529, timeReceived: 1509371566235291000, numWidgets: 23
appInstanceId uniquely identifies an App Instance (not re-used across clouds). New documents are sent every X secs. I'm trying to get the current total Widgets per Cloud (bold records above are for same App Instance, want the latest for the App Instance). To accomplish this I'm trying the following:
Term agg by cloud (to group by Cloud) named "cloud"
Term subagg (to group by App Instance) named "app_instance"
Top Hits subagg (to get the latest document per App Instance based on the time received desc) named "latest_hit"
Sum Bucket pipeline agg (sibling to the App Instance Top Hits aggs to sum the latest App Instance docs for the Cloud) named "cloud_widgets"
I cannot figure out (if it's even possible) how to path it to get the numWidgets. I get an error that the path is not supported -> "path not supported for [latest_hit]: [numWidgets]".
I'm new to Elasticsearch and may be taking the wrong approach. Query and output (without the sum_bucket since it doesn't work) are below. Any assistance would be appreciated.
Query...
{
"size": 0,
"_source" : ["appInstanceId", "cloud", "instance", "timeReceived", "numWidgets"],
"aggs": {
"cloud": {
"terms": {
"field": "cloud",
"size": 10000
},
"aggs": {
"app_instance" : {
"terms": {
"field": "appInstanceId",
"size": 10000
},
"aggs": {
"latest_hit": {
"top_hits": {
"_source" : {
"includes": ["numWidgets"]
},
"sort": [{
"timeReceived": {
"order": "desc"
}
}],
"size": 1
}
}
}
},
"cloud_widgets": {
"sum_bucket": {
"buckets_path" : "app_instance>latest_hit.numWidgets"
}
}
}
}
}
}
Output...
{
"took": 4866,
"hits": {
"total": 31049103,
"max_score": 0,
"hits": []
},
"aggregations": {
"cloud": {
"buckets": [
{
"key": 1,
"doc_count": 1295244,
"app_instance": {
"buckets": [
{
"key": 123,
"doc_count": 53976,
"latest_hit": {
"hits": {
"total": 53976,
"max_score": null,
"hits": [
{
"_index": "my-index-2017.10.23",
"_type": "doc",
"_id": "AV9LrmOT4sZ0l4JseyWZ",
"_score": null,
"_source": {
"numWidgets": 102
}
}
]
}
}
},
{
"key": 802,
"doc_count": 53975,
"latest_hit": {
"hits": {
"total": 53975,
"max_score": null,
"hits": [
{
"_index": "my-index-2017.10.23",
"_type": "doc",
"_id": "AV9LrmRw4sZ0l4JseyYE",
"_score": null,
"_source": {
"numWidgets": 45
}
}
]
}
}
}
]
},
{
"key": 2,
"doc_count": 1295211,
"app_instance": {
"buckets": [
{
"key": 267,
"doc_count": 53979,
"latest_hit": {
"hits": {
"total": 53979,
"max_score": null,
"hits": [
{
"_index": "my-index-2017.10.23",
"_type": "doc",
"_id": "AV9LrmOT4sZ0l4JseyWd",
"_score": null,
"_source": {
"numWidgets": 88
}
}
]
}
}
},
{
"key": 529,
"doc_count": 53978,
"latest_hit": {
"hits": {
"total": 53978,
"max_score": null,
"hits": [
{
"_index": "my-index-2017.10.23",
"_type": "doc",
"_id": "AV9LrmRw4sZ0l4JseyXq",
"_score": null,
"_source": {
"numWidgets": 23
}
}
]
}
}
}
]
}
}
}
}
}
}