elasticsearch - sum of aggregated values

Question

Let's say I have the following data

{
    "quantity" : 1,
    "amount" : 10,
    "discount" : 1
}

{
    "quantity" : 4,
    "amount" : 12,
    "discount" : 2
}

I can get the aggregated results of these using the following query,

{
   ...
   "aggs" : {
     "sumOfQuantity" : {
        "sum" : {
           "field" : "quantity"
        }
     },
     "SumOfAmount" : {
        "sum" : {
           "field" : "amount"
        }
     },
     "SumOfDiscount" : {
        "sum" : {
           "field" : "discount"
        }
     }
   }
}

Using the above query, I can get the sum of the required fields. Now I further want to subtract the aggregated SumOfAmount and SumOfDiscount (SumOfAmount - SumOfDiscount). How can I achieve it?

Thanks in advance!!

Answer 1

You can use run time mapping

Query

{
  "size": 0,
  "runtime_mappings": {
    "net_amount": {
      "type": "double",
      "script": {
        "source": "emit(doc['amount'].value - doc['discount'].value)"
      }
    }
  },
  "aggs": {
    "sumOfQuantity": {
      "sum": {
        "field": "quantity"
      }
    },
    "SumOfAmount": {
      "sum": {
        "field": "amount"
      }
    },
    "SumOfDiscount": {
      "sum": {
        "field": "discount"
      }
    },
    "NetAmmount":{
      "sum": {
        "field": "net_amount"
      }
    }
  }
}

Result

 "aggregations" : {
    "sumOfQuantity" : {
      "value" : 5.0
    },
    "NetAmmount" : {
      "value" : 19.0
    },
    "SumOfAmount" : {
      "value" : 22.0
    },
    "SumOfDiscount" : {
      "value" : 3.0
    }
  }