CODEWITHSUNDEEP
elasticsearch
arpan
arpan

Reputation: 139

How to subtract two values in Elasticsearch

I'm trying to get the difference between two fields. I'm using Elasticsearch 5.5.

I have already tried

{
"query": {
"bool": {
"filter": {
"script": {
"script": "doc['students.total_fee'].value - doc['students.paid_fee'].value"
}
}
}
}
}

but it is returning empty "hits".

I have also tried

{
"query": {
"bool": {
"must": [
{
"nested": {
"path": "students",
"query": {
"script": {
"script": {
"inline": "doc['students.total_fee'].value - doc['students.paid_fee'].value",
"lang": "expression"
}
}
}
}
}
]
}
}

and it is returning "0".

also the "script_fields" did not worked.

{ "script_fields" :
 { "difference" :
 { "script" : "doc['students.total_fee'].value - doc['students.paid_fee'].value"
 } } }

suppose I have data in following format.

"_source" : {
"students" : [
{
"name" : "A",
"total_fee" : 12345,
"paid_fee" : 12344.8
},
{
"name" : "B",
"total_fee" : 23456,
"paid_fee" : 23455.6
}
]
}

Now I want to get the difference between "total_fee" and "paid_fee" for each student.

I expect to get an array of differences for all students.

Thanks in advance :D

Upvotes: 0

Views: 3880

Answers (1)

Sounak Saha
Sounak Saha

Reputation: 943

You need to use the below query and my es version is 6.2.2. It is giving a perfect result. But remember scripting is generally CPU intensive.

If you normal field then below query working fine.

{
   "size": 10,
    "script_fields": {
      "fare_diff": {
        "script": "doc[\"students.total_fee\"].value  - doc[\"students.paid_fee\"].value"
      }
    }
  }

If you are using a nested field parameter then the query would be like below.

{
    "script_fields": {
        "fare_diff": {
            "script": {
            "lang": "painless",
            "source":  "int total = 0; def l = new ArrayList(); for (int i = 0; i < params['_source']['students'].size(); ++i) { l.add(params['_source']['students'][i]['total_fee'] - params['_source']['students'][i]['paid_fee']);} return l.toArray();"
       }
    }
}
}

Reason: Because nested documents are indexed as separate documents, they can only be accessed within the scope of the nested query, the nested/reverse_nested aggregations, or nested inner hits.

Upvotes: 1

Related Questions