Elasticsearch - mutiplication of 2 fields and then sum aggregation

Question

I'm trying to make a simple query in elasticsearch but I can't figure out how to do it. I searched all over the internet and there was no discussion on this situation.

Let's say I have items like those:

{
  "item_id": 1,
  "item_price": 100,
  "item_quantity": 2
},
{
  "item_id": 2,
  "item_price": 200,
  "item_quantity": 3
},
{
  "item_id": 3,
  "item_price": 150,
  "item_quantity": 1
},
{
  "item_id": 4,
  "item_price": 250,
  "item_quantity": 5
}

I want to make a query that will give me the result of the total price in the stock.

for example: 100*2 + 200*3 + 150*1 + 250*5

the result for this query supposed to be 2,200

---

The answer query for the last data is working, But what about this complex situation:

POST tests/test2/
{
  "item_category": "aaa",
  "items": 
  [
    {
      "item_id": 1,
      "item_price": 100,
      "item_quantity": 2
    },
    {
      "item_id": 2,
      "item_price": 150,
      "item_quantity": 4
    }
  ]
}

POST tests/test2/
{
  "item_category": "bbb",
  "items": 
  [
    {
      "item_id": 3,
      "item_price": 200,
      "item_quantity": 3
    },
    {
      "item_id": 4,
      "item_price": 200,
      "item_quantity": 5
    }
  ]
}

POST tests/test2/
{
  "item_category": "ccc",
  "items": 
  [
    {
      "item_id": 5,
      "item_price": 300,
      "item_quantity": 2
    },
    {
      "item_id": 6,
      "item_price": 150,
      "item_quantity": 8
    }
  ]
}

POST tests/test2/
{
  "item_category": "ddd",
  "items": 
  [
    {
      "item_id": 7,
      "item_price": 80,
      "item_quantity": 10
    },
    {
      "item_id": 8,
      "item_price": 250,
      "item_quantity": 4
    }
  ]
}

In this case the next query is not working and give me a wrong answer (1,420 instead of 6,000):

GET tests/test2/_search
{
  "query": {
    "match_all": { }
  },
    "aggs": {
        "total_price": {
            "sum": {
                "script": {
                    "lang": "painless",
                    "inline": "doc['items.item_price'].value * doc['items.item_quantity'].value"
                }
            }
        }
    }
}

Answer 1

You can use sum aggregation for values calculated using script

{
    "aggs": {
        "total_price": {
            "sum": {
                "script": {
                    "lang": "painless",
                    "inline": "doc['item_price'].value * doc['item_quantity'].value"
                }
            }
        }
    }
}

Take a look here https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-sum-aggregation.html#_script_9 for more details

Update

As for your advanced case, it would be better to map your items field as nested type, after that you can use this aggregation

{
    "aggs": {
        "nested": {
            "nested": {
                "path": "items"
            },
            "aggs": {
                "total_price": {
                    "sum": {
                        "script": {
                            "inline": "doc['items.item_price'].value * doc['items.item_quantity'].value"
                        }
                    }
                }
            }
        }
    }
}

this is the mapping query for the example DB in the question:

PUT tests
{
  "mappings": {
    "test2": {
      "properties": {
        "items": {
          "type": "nested" 
        }
      }
    }
  }
}

just to clarify, You must make the mapping query before the index has been created. (changing mapping for existing field is not allowed).