Elasticsearch: Finding the average value of a column

[marklit]

I have a column of floating point values that I want to find the average of. I suspected this might be the best way to query ES:

params = {
    "query" : {
        "match_all" : {}
    },
    "aggs" : {
        "avg_grade" : { "avg" : { "script" : "doc['weight1'].value" } }
    }
}

resp = requests.post('http://127.0.0.1:9200/myindex/_search?q=country:united_kingdom', data=json.dumps(params))

Returns the following:

{
    "error": "SearchPhaseExecutionException[Failed to execute phase [query], all shards failed;
    shardFailures {[li87cVMZTt6PDTJj1CXtsA][myindex][1]: QueryPhaseExecutionException[[myindex][1]: query[country:united_kingdom],from[0],size[10]: Query Failed [Failed to execute main query]];
    nested: AggregationExecutionException[Unsupported script value [0.207]]; }{[li87cVMZTt6PDTJj1CXtsA][myindex][2]: QueryPhaseExecutionException[[myindex][2]: query[country:united_kingdom],from[0],size[10]: Query Failed [Failed to execute main query]]; 
    nested: AggregationExecutionException[Unsupported script value [0.106]]; }{[li87cVMZTt6PDTJj1CXtsA][myindex][0]: QueryPhaseExecutionException[[myindex][0]: query[country:united_kingdom],from[0],size[10]: Query Failed [Failed to execute main query]];
    nested: AggregationExecutionException[Unsupported script value [0.107]]; }{[li87cVMZTt6PDTJj1CXtsA][myindex][3]: QueryPhaseExecutionException[[myindex][3]: query[country:united_kingdom],from[0],size[10]: Query Failed [Failed to execute main query]];
    nested: AggregationExecutionException[Unsupported script value [0.924]]; }{[li87cVMZTt6PDTJj1CXtsA][myindex][4]: QueryPhaseExecutionException[[myindex][4]: query[country:united_kingdom],from[0],size[10]: Query Failed [Failed to execute main query]];
    nested: AggregationExecutionException[Unsupported script value [2.767]]; }]",
    "status": 500
}

The AggregationExecutionException[Unsupported script value [0.106]] makes me think I'm probably referencing { "avg" : { "script" : "doc['weight1'].value" } } the wrong way but I can't see how else I could reference this in any of the documentation. Any ideas?

[dadoonet]

Could you ask your question on the mailing list? We can definitely help there.

We use github issues for issues and feature requests.

Thanks!

Wondering why you are using scripts though. As you can probably compute avg directly on weight1 without needing a script. But let's continue this discussion on the mailing list.

[taf2]

I assume this is the related conversation https://discuss.elastic.co/t/how-to-get-only-aggregation-values-from-elasticsearch/17674/8

[taf2]

Upgrading from 1.7 to 2.x

      {
        sum: {
          script: "doc['#{field}'].empty ? 0 : Float.valueOf(doc['#{field}'].value)"
        }
      }

The above raises an exception

Caused by: groovy.lang.MissingPropertyException: No such property: Float for class: d3c10a0f498c5e77a152dc524e0fbe131cc46ca2
        at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:53)
        at org.codehaus.groovy.vmplugin.v7.IndyGuardsFiltersAndSignatures.unwrap(IndyGuardsFiltersAndSignatures.java:177)
        at org.codehaus.groovy.vmplugin.v7.IndyInterface.selectMethod(IndyInterface.java:218)
        at d3c10a0f498c5e77a152dc524e0fbe131cc46ca2.run(d3c10a0f498c5e77a152dc524e0fbe131cc46ca2:1)
        at org.elasticsearch.script.groovy.GroovyScriptEngineService$GroovyScript$1.run(GroovyScriptEngineService.java:311)
        at java.security.AccessController.doPrivileged(Native Method)
        at org.elasticsearch.script.groovy.GroovyScriptEngineService$GroovyScript.run(GroovyScriptEngineService.java:308)
        ... 23 more

Removing the Float.value from the expression e.g.

      {
        sum: {
          script: "doc['#{field}'].empty ? 0 : doc['#{field}'].value"
        }
      }

Yields:

RemoteTransportException[[ctm.local.dev][127.0.0.1:9300][indices:data/read/search[phase/query]]]; nested: QueryPhaseExecutionException[Query Failed [Failed to execute main query]]; nested: AggregationExecutionException[Unsupported script value [25.0]];
Caused by: QueryPhaseExecutionException[Query Failed [Failed to execute main query]]; nested: AggregationExecutionException[Unsupported script value [25.0]];
        at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:409)
        at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:113)
        at org.elasticsearch.search.SearchService.loadOrExecuteQueryPhase(SearchService.java:364)
        at org.elasticsearch.search.SearchService.executeQueryPhase(SearchService.java:376)
        at org.elasticsearch.search.action.SearchServiceTransportAction$SearchQueryTransportHandler.messageReceived(SearchServiceTransportAction.java:368)
        at org.elasticsearch.search.action.SearchServiceTransportAction$SearchQueryTransportHandler.messageReceived(SearchServiceTransportAction.java:365)
        at org.elasticsearch.transport.TransportService$4.doRun(TransportService.java:350)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: AggregationExecutionException[Unsupported script value [25.0]]
        at org.elasticsearch.search.aggregations.support.values.ScriptDoubleValues.setDocument(ScriptDoubleValues.java:74)
        at org.elasticsearch.search.aggregations.metrics.sum.SumAggregator$1.collect(SumAggregator.java:79)
        at org.elasticsearch.search.aggregations.LeafBucketCollector$3.collect(LeafBucketCollector.java:73)
        at org.elasticsearch.search.aggregations.bucket.BucketsAggregator.collectExistingBucket(BucketsAggregator.java:80)
        at org.elasticsearch.search.aggregations.bucket.terms.GlobalOrdinalsStringTermsAggregator$2.collect(GlobalOrdinalsStringTermsAggregator.java:130)
        at org.elasticsearch.search.aggregations.LeafBucketCollector.collect(LeafBucketCollector.java:88)
        at org.apache.lucene.search.MultiCollector$MultiLeafCollector.collect(MultiCollector.java:173)
        at org.apache.lucene.search.Weight$DefaultBulkScorer.scoreAll(Weight.java:218)
        at org.apache.lucene.search.Weight$DefaultBulkScorer.score(Weight.java:169)
        at org.apache.lucene.search.BulkScorer.score(BulkScorer.java:39)
        at org.apache.lucene.search.IndexSearcher.search(IndexSearcher.java:821)
        at org.apache.lucene.search.IndexSearcher.search(IndexSearcher.java:535)
        at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:384)
        ... 10 more

[elshobokshy]

I'm having the same issue. So this question has not been replied to yet? @dadoonet more than one person seems to have this issue, could you please respond to it here? So people in the future when searching for an answer find this? As there's no other place where this question has been replied.