I have the following query where get the a data and I create an aggregation of each past hour:

    query = {
        "query": {
            "bool": {          
                "must": [
                    { "term": {"deviceId":device} },
                    { "match": {"eventType":"Connected"} } 
                ],
                "must_not":[{
                        "query_string": {
                            "query": "Pong",
                            "fields": ["data.message"]
                        }
                    },
                ] 
            },

        },
        "size": 0,
        "sort": [{ "timestamp": { "order": "desc" }}],
        "aggs" : {
            "time_buckets" : {
                "date_histogram" : {
                    "field" : "timestamp",
                    "interval" : "hour",

                },
            }
        }
    }

I would like to get the average of a field from each hour interval (each bucket created by the aggregation). In this article they talk about something similar with what I wish to do: .html ("What was the average latency of our website every hour in the last week?"). However, they don't explain exactly what to do in this case.

Does anyone know how to do that?

I have the following query where get the a data and I create an aggregation of each past hour:

    query = {
        "query": {
            "bool": {          
                "must": [
                    { "term": {"deviceId":device} },
                    { "match": {"eventType":"Connected"} } 
                ],
                "must_not":[{
                        "query_string": {
                            "query": "Pong",
                            "fields": ["data.message"]
                        }
                    },
                ] 
            },

        },
        "size": 0,
        "sort": [{ "timestamp": { "order": "desc" }}],
        "aggs" : {
            "time_buckets" : {
                "date_histogram" : {
                    "field" : "timestamp",
                    "interval" : "hour",

                },
            }
        }
    }

I would like to get the average of a field from each hour interval (each bucket created by the aggregation). In this article they talk about something similar with what I wish to do: http://www.elasticsearch/guide/en/elasticsearch/guide/current/_looking_at_time.html ("What was the average latency of our website every hour in the last week?"). However, they don't explain exactly what to do in this case.

Does anyone know how to do that?

• javascript
• elasticsearch
• average
• datahistory

1 Answer 1

Just realized that I could do a nested aggregation and then calculate the average of a field inside a aggregation. Here is what I did and it's working properly now:

 query = {
            "query": {
                "bool": {          
                    "must": [
                        { "term": {"deviceId":device} },
                        { "match": {"eventType":"Connected"} } 
                    ],
                    "must_not":[{
                            "query_string": {
                                "query": "Pong",
                                "fields": ["data.message"]
                            }
                        },
                    ] 
                },

            },
            "size": 0,
            "sort": [{ "timestamp": { "order": "desc" }}],
            "aggs" : {
                "time_buckets" : {
                    "date_histogram" : {
                        "field" : "timestamp",
                        "interval" : "day"
                    },
                    "aggs" : {
                        "avg_battery" : {
                            "avg": { "field": "data.battery-level" } 
                        }
                    }
                }
            }
        }