Hi
Currently i am trying to group a field based on one field and than getting sum of other fields with respect to the respective field used for grouping. I want to get a new value which needs to be division of the summed field . I will provide the current query i have :
Query1:
GET /index1/table1/_search
{
"size": 0,
"query": {
"filtered": {
"query": {
"query_string": {
"query": "*",
"analyze_wildcard": true
}
},
"filter": {
"bool": {
"must": [
{
"range": {
"start_timestamp": {
"gte": 1430483327935,
"lte": 1432988927935
}
}
}
],
"must": [
{"term": {
"account_id": 29
}
}
],
"must_not": []
}
}
}},
"aggs": {
"custom_name": {
"terms": {
"field": "a_name"
},
"aggs": {
"spe": {
"sum": {
"field": "spend"
}
},"gained": {
"sum": {
"field": "gain"
}
}
}
}
}
}
Now in my query i am aggregating them based on the field ("a_name") and summing "spend" and "gain". I want to get a new field which would be ratio of sum (spend/gain)
**
Basically I would like to divide "spe" and "gained" and get another field "ratio"
**
I also tried adding script but i am getting NaN , also to enable this; i had to enable them first in elasticsearch.yml file
script.engine.groovy.inline.aggs: on
Query2
GET /index1/table1/_search
{
"size": 0,
"query": {
"filtered": {
"query": {
"query_string": {
"query": "*",
"analyze_wildcard": true
}
},
"filter": {
"bool": {
"must": [
{
"range": {
"start_timestamp": {
"gte": 1430483327935,
"lte": 1432988927935
}
}
}
],
"must": [
{"term": {
"account_id": 29
}
}
],
"must_not": []
}
}
}},
"aggs": {
"custom_name": {
"terms": {
"field": "a_name"
},
"aggs": {
"spe": {
"sum": {
"field": "spend"
}
},"gained": {
"sum": {
"field": "gain"
}
},
"rati":{
"sum":{ "script":"doc['spend'].value/doc['gain'].value"
}
}
}
}
}
}
This particular query is showing me a 'NaN' in output.
Thanks!