Elastic search nested aggrigation

Elastic Stack Elasticsearch
,
1

I am try to get final price of each product of orders bu not able to calculate.
The Document sample :

Order Index :
-------------
{
  "order_id": "123",
  "customer": "John Doe",
  "deliveryFee": 40,
      "items": [
      {
          "product": "Laptop",
          "price": 1200,
          "quantity": 1
      },
      {
          "product": "Mouse",
          "price": 25,
          "quantity": 1
      }
  ]
}

The Response i am expecting (product wise Total price like below ) :

{
  "product":{
    buckets:[
      {
        "key":Laptop,
        "Price" : 1240  //(price + deliveryfee)*quantity
      },
      {
        "key":Mouse,
        "Price" : 65  //(price + deliveryfee)*quantity
      }
    ]
  }
}

The Query :


{
  "aggs": {
    "AllProductPrice": {
      "nested": {
        "path": "items"
      },
      "aggs": {
        "product":{
          "term":{
            "field": "items.product.keyword",
            "size": 10
          }
        },
        "aggs":{
          "totalProductPrice": {
            "sum": {
              "script": {
                "source":"return (doc['deliveryFee'].value * doc['item.Price']) * doc['item.quantity']"
              }
            }
          }
        }
      }
    }
  }
}

The Main challanges is we are not able to get doc['deliveryFee'].value . Because we have used nested path aggrigation. So i used back reverse nested But its also not working.2024-12-08T18:30:00Z