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"
                }
            }
        }
    }
}

Frentz

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).

Marijn

Great, It's working for this case, but my situation is a little bit more complex and I tried the same algorithm but it fails to bring to right answer. I'm updating my question to the real situation that I need to Sum, Please take a look to see what I did wrong. – Frentz

Hi, Thanks for your answer but it gives an ERROR:."[nested] nested path [items] is not nested", I tried to fix it with no success. do you have any idea what it can be? – Frentz

As I said, you should change your mapping to make this field nested. Take a look here elastic.co/guide/en/elasticsearch/reference/current/nested.html – Marijn

Thanks for you answer, very helpful and gave me the answer I need. Please accept the edit for your answer, so beginners like me will understand the mapping thing. – Frentz