Search for results based on aggregations

The use of aggregation is popular for the use case: In this date range, how many visits we have in total?

However, what should I do when I want something reverse: On which days does I have the visits range from x to y?

I can think of we still have to use Aggregation to compute total visits of each day, and from that result, we will do a Range Aggregation to filter the result. But I can not come up with a query.

Thanks a lot for your help!

The equivalent SQL might be:

SELECT state, SUM(pop) AS totalPop
FROM zipcodes
GROUP BY state
HAVING totalPop >= (10*1000*1000)

We can solve this with MongoDB:

db.zipcodes.aggregate( [
 { $group: { _id: "$state", totalPop: { $sum: "$pop" } } },
 { $match: { totalPop: { $gte: 10*1000*1000 } } }
] )

However, how we can solve this with elasticsearch is still mysterious for me...

I think you may want pipeline aggs, which is coming in 2.0 :slight_smile:

1 Like

Thanks @warkolm, so it is completely impossible in 1.x? Do you have any idea of when version 2.0 will be released?

There is something like https://github.com/elastic/elasticsearch/pull/11941 (not merged yet) that would be the closest thing to HAVING elasticsearch would have.

In 1.x, one option would be to sort your "state" terms aggregation by the value of its sub "totalPop" aggregation and look at the top buckets.

Thanks @jpountz :wink: