ES SQL :: Syntax for "sum( Value1 * Value2)"?

Hi Elasticsearch Ninja Masters,

I’ve recently spun up an Elasticsearch v7.4.0 Docker container, and I’m using the “elasticsearch-sql-cli” script to run SQL-like queries off my ES instance. Which is awesome, thank you for implementing that.

I have a specific syntax question, however. I need to run the below query:

select Value1, sum( Value2 * Value3 )
from \"myindex2019.10.09\"
group by Value1;

Pretty simple, really. For every record in the “myindex2019.10.09” index, multiply the values of Value2 and Value3 together. Then sum up all the (Value2 * Value3)’s together, grouped my Value1. Should look like this:

Value1 | sum(Value2 * Value3)
-------+---------------------
1      |  100
2      |  200
3      |  ...etc...

But from experimentation, I can tell you that the “elasticsearch-sql-cli” script likes Value2 * Value3 or sum(Value2), but not sum(Value2 * Value3) The full output I see when I try this query is below. The “elasticsearch-sql-cli” looks like it throws a Java exception, but does not crash.

Is there a solution here? Any suggestion would be useful. Thanks!

sql> select Value1, sum( Value2 * Value3 ) from \"myindex2019.10.09\";
Server error [Server encountered an error [Does not know how to convert argument Mul[] for function Sum[]]. [SqlIllegalArgumentException[Does not know how to convert argument Mul[] for function Sum[]]
        at org.elasticsearch.xpack.sql.planner.QueryTranslator.field(QueryTranslator.java:470)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:854)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:850)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator$SingleValueAggTranslator.asAgg(QueryTranslator.java:971)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator$AggTranslator.apply(QueryTranslator.java:961)
        at org.elasticsearch.xpack.sql.planner.QueryTranslator.toAgg(QueryTranslator.java:198)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.addAggFunction(QueryFolder.java:445)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:359)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:204)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:196)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:190)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:196)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:593)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:589)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.<init>(RuleExecutor.java:82)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:158)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.execute(RuleExecutor.java:130)
        at org.elasticsearch.xpack.sql.planner.QueryFolder.fold(QueryFolder.java:82)
        at org.elasticsearch.xpack.sql.planner.Planner.foldPlan(Planner.java:38)
        at org.elasticsearch.xpack.sql.planner.Planner.plan(Planner.java:28)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$physicalPlan$4(SqlSession.java:160)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:156)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:144)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:277)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:70)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:88)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:101)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:97)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:70)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:261)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:247)
        at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse(TransportService.java:1110)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse(TransportService.java:1188)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1168)
        at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:54)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:47)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:30)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction.lambda$asyncShardOperation$0(TransportSingleShardAction.java:110)
        at org.elasticsearch.action.ActionRunnable$1.doRun(ActionRunnable.java:45)
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:773)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:830)
]]
sql>

Pete,

Unfortunately, I think you're hitting a documented limitation of our SQL support:

Using aggregation functions on top of scalar functions

Aggregation functions like MIN , MAX , etc. can only be used directly on fields, and so queries like SELECT MAX(abs(age)) FROM test are not possible.

For what it's worth, I popped the following documents into an index called myindex:

{
    "key": "value",
    "length": 2,
    "width": 3
}
{
    "key": "value",
    "length": 5,
    "width": 7
}

And used the Elasticsearch query DSL to get the sum of areas using the following query:

GET myindex/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "keys": {
      "terms": {
        "field": "key.keyword"
      }, 
      "aggs": {
        "areas": {
          "sum": {
            "script": "doc['length'].value * doc['width'].value"
          }
        }
      }
    }
  },
  "size": 0
}

Result:

{
  [...],
  "aggregations" : {
    "keys" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "value",
          "doc_count" : 2,
          "areas" : {
            "value" : 41.0
          }
        }
      ]
    }
  }
}

I wish I had been able to find a SQL solution for you.

-William

Indeed, this is not supported at the moment.
There is a github issue created for something very similar: https://github.com/elastic/elasticsearch/issues/37271

1 Like

No problem, I appreciate the information. Sometimes its nice to know that the thing you want to do isn't possible at the moment. Much appreciated!

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.