Hi, Filtering based on results of aggregations is not supported unfortunately. There is no way to do the equivalent of your SQL query.
On Tue, May 27, 2014 at 4:06 AM, Choon Keat Chew <[email protected]>wrote: > I have an index full of user transactions. credit is the amount of an > individual transaction. To get a list of users with most total credits I > have this working fine > > { > "size": 0, // only interested in aggregation info > "aggs": { > "users": { > "terms": { > "field": "user_id", > "order": { > "total_sum.sum": "desc" > } > }, > "aggs": { > "total_sum": { > "sum": { > "field": "credit" > } > } > } > } > } > } > > which is roughly equivalent in SQL: > SELECT user_id, SUM(credit) FROM index ORDER BY SUM(credit) DESC LIMIT 10; > > I'm having problems modifying my ES query to filter by total_sum.sum , > which in SQL equivalent, it would be: > SELECT player_id, SUM(points) FROM index *HAVING SUM(POINTS) BETWEEN 10 > AND 15* ORDER BY SUM(POINTS) DESC LIMIT 10; > > My attempts inserting range filters fails in syntax > > { > "size": 0, > "aggs": { > "users": { > "terms": { > "field": "user_id", > "order": { > "total_sum.sum": "desc" > } > }, > "aggs": { > "having": { > "filter": { > "range": { > "total_sum.sum": { > "gte": 10, "lte": 15 > } > } > }, > "aggs": { > "total_sum": { > "sum": { > "field": "credit" > } > } > } > } > } > } > } > } > > get errors > > { > "error": "SearchPhaseExecutionException[Failed to execute phase > [query_fetch], all shards failed; shardFailures ... > [search/phase/query+fetch]]; nested: AggregationExecutionException[terms > aggregation [users] is configured with a sub-aggregation order [total_sum] > but no sub aggregation with this name is configured]; }]", > "status": 500 > } > > Is such a range filter possible? > > -- choonkeat > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com<https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- Adrien Grand -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6JpuT%3DZpPwxqz3QL%2Bv5Q4RqH10GSEm7xmjDDXX3mbTAA%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
