Elasticsearch sum based on condition

Question

I have this mock data which I want grouped by name then have a total field that is the sum of the value fields only if the status is won.

[{
  name: 'Foo',
  value: 12,
  status: 'won'
},
{
  name: 'Foo',
  value: 2,
  status: 'lost'
},
{
  name: 'Foo',
  value: 10,
  status: 'won'
},
{
  name: 'Bar',
  value: 4,
  status: 'won'
}]

I am able to group by name and obtain the total sum of the value fields but have not figured out how to only sum the won cases.

aggs: {
  by_name: {
    terms: {
      field: 'name'
    },
    aggs: {
      total_value: {
        sum: {
          field: 'value' // What I want is value if status == 'won' 
        }
      }
    }
  }

My desired result should look like:

[{
  name: 'Foo',
  total_value: 22 // Currently 24
}, {
  name: 'Bar',
  total_value: 4
}]

This seems like a common used case but while I have found lots of info on filtering but not this particular case.

What is the expected result?. Aggregation or matching documents. — Sathishkumar
– Sathishkumar, Commented Sep 28, 2019 at 23:09
I want all the results returned but grouped by "name". For each entry the "total_value" should be the sum of all their "won" values. I got it to work with a script so I may be all good — cyberwombat
– cyberwombat, Commented Sep 28, 2019 at 23:15
Please add your findings here. So it helps us too in the future. — Sathishkumar
– Sathishkumar, Commented Sep 28, 2019 at 23:17
Yes. Added an answer - perhaps there is a more efficient way than running a script? — cyberwombat
– cyberwombat, Commented Sep 28, 2019 at 23:20

cyberwombat · Accepted Answer · 2019-09-29 03:26:03Z

OK I found two ways to do this.

1. Using a script

ES supports various scripting languages but has built in support for Painless:

aggs: {
  by_name: {
    terms: {
      field: 'name'
    },
    aggs: {
      total_value: {
        sum: {
          script: {
           lang: 'painless',
           source:doc['status'].value == 'won' ? doc['value'] : 0"
          }
        }
      }
    }
  }

2. Using nested grouping/aggregation

In my use case I also need to total up all the won and lost as individual field to get a result set more like:

[{
  name: 'Foo',
  total_won_value: 22,
  total_won: 2
  total_lost_value: 2,
  total_lost: 1
}, {
  ...
}

While this can be done with a few scripts I suspect (This would have to be tested though) its more performant to achieve this with nested aggregation.

aggs: {
  by_name: {
    terms: {
      field: 'name'
    },
    aggs: {
      by_status: {
        terms: {
          field: 'status'
        },
        aggs: {
          total_value_by_status: {
            sum: {
              field: 'value'
            }
          }
        }
      }
    }
  }
}

The drawback of the second method is that its a bit harder to parse the results especially in something like AppSync templates.

取一个好的名字 · Accepted Answer · 2021-09-13 08:49:55Z

0

"total_value": {
      "sum": {
        "script":"params.status =='won'?params.value:0"
      }
    }

answered Sep 13, 2021 at 8:49

取一个好的名字

1,84316 silver badges18 bronze badges

1 Comment

Roman Over a year ago

Please provide a short description how your answer solves the problem. thank you!

Collectives™ on Stack Overflow

Elasticsearch sum based on condition

2 Answers 2

1. Using a script

2. Using nested grouping/aggregation

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1. Using a script

2. Using nested grouping/aggregation

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related