Is it possible to perform calculations using multiple fields in a document

Elastic Stack Elasticsearch
1

I am new to elasticsearch, and trying to evaluate if my sql query can be migrated to elastic search.

  1. Is it possible to write an elasticsearch query that returns calculations performed using multiple fields in a document?
    Ex: if I have a document like {"salary": 100000, "spouse_salary":200000} , I want the query result to give me a field called total_salary with a value of salary+spouse_salary (300000 in this case)

  2. Is it possible to perform calculations as stated above but with also user supplied parameters into the mix?
    Ex: If the tax on the salary needs to be calculated before the results are returned, and the tax % is passed in the query(I am not sure how though, is there a way to do that ? ), If i pass .05 as tax_rate to the query, i want a response like total_tax = 300000 * .05

I have skimmed through the documentation, but most of the aggregations/calculations appear to be on a single field like sum of salary, avg of salary etc, but couldn't find calculations using multiple fields, like salary+spouse_salary

Any pointers is appreciated.

2

There is probably two options. First option, do this calculation in your client application. Second option, use a scripted metric. Yep, nearly all built-in aggregations work on single fields only.

3

You could also use a script in the sum aggregation itself to achieve what you describe above. see here for more information: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-sum-aggregation.html#_script_3

In your case you could do something like:

{
    ...,

    "aggs" : {
        "salary" : { "sum" : { "script" : "doc['salary'].value + doc['spouse_salary'].value" } }
    }
}
2 Likes
4

Thank you Joshua, colings86,
I am exploring writing Java plugin for my requirement.