Help with ES 1.x aggregation range filter over "sum" value


(Choon Keat Chew) #1

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 elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Filtering based on results of aggregations is not supported
(Adrien Grand) #2

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 choonkeat@gmail.comwrote:

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 elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.comhttps://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 elasticsearch+unsubscribe@googlegroups.com.
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.


(system) #3