Search for results based on aggregations

(Ptgamr) #1

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!

(Ptgamr) #2

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...

(Mark Walkom) #3

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

(Ptgamr) #4

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

(Adrien Grand) #5

There is something like (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.

(Ptgamr) #6

Thanks @jpountz :wink:

(system) #7