How to put the aggregation value as query condition

Elastic Stack Elasticsearch
1

I have one request that will calculate the average responsetime of the http request in a range time period. But I need make a filter only to get the ones which response times greater than a specific value.
Here is the SQL:

Select * avg(response_time) as avg_responsetime, clientip
where avg_responsetime>5
group by clientip.

I can got the avg response time by the elasticsearch aggregation avg. But I have no idea to add the query condition "where avg_responsetime>5"

Anyone can help me?

Here is my elasticsearch query:

{
"query" : {
"filtered": {
"filter": {
"range": {
"@timestamp": {
"from": 1430986911476,
"to": 1430987211476
}
}
}
}
},

"aggs": {
    "group_by_request": {
        "terms": {
            "field": "request" ,
            "order": { "avg_responsetime": "desc"}
        },
        "aggs": {
            "avg_responsetime": {
                "avg": {
                    "field": "response_time"
                }
            }
        }
    }
}

}

2

At the moment, there is no way to accomplish this inside of Elasticsearch. You'll have to apply your "Where" conditions in your application code, manually filtering the response. Sorry :disappointed:

In the future, we hope to provide this via functionality being added in #9876. Informally we are calling it a "Having Aggregation", but the design is a bit tricky so it is still being prototyped. Essentially, it executes at the end of the aggregation phase and prunes the response tree to buckets that match variously configured thresholds.

The framework powering these features was recently merged into master, so now it's "just" down to implementing the various aggregations. So hope is on the horizon!

1 Like
3

@polyfractal, Thanks your quick response! You answer is very helpful for me! I will handle the where condition in my codes. Very looking forward to your new feature.