Adding new value after calculation in Aggregation


(Aditya Patel) #1

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!


(system) #2