CODEWITHSUNDEEP
elasticsearch
Leonardo
Leonardo

Reputation: 11387

Elasticsearch - Sum with group by

I couldn't apply the concept of chain aggregation... i need help with this scenario:
My documents look like this:

{
  "date":"2019-01-30",
  "value":1234.56,
  "partnerId":9876
}

and i would like to filter by date (month) and summarize them by partner Id, and then count it, obtaining a result like:

{
   "partnerId": 9876,
   "totalValue": 12345567.87,
   "count": 6574
}

How would this query look like?

Upvotes: 0

Views: 3978

Answers (1)

Nishant
Nishant

Reputation: 7854

What you are trying to achieve can be done by sub aggregation, in other words aggregation inside aggregation.

For your case first you want to group by parternId, so you will require terms aggregation on parternId field. Lets call this aggregation as partners. This will give you two values of your expected result, parternId and count.

Now for each of the groups (bucket) of partnerId, totalValue is required i.e. sum of value for each partnerId. This can be done by adding sum aggregation inside term aggregation partners. So the final query along with the filter for date (month) will be:

{
  "query": {
    "bool": {
      "filter": {
        "range": {
          "date": {
            "gte": "2019-01-01",
            "lte": "2019-01-31"
          }
        }
      }
    }
  },
  "aggs": {
    "partner": {
      "terms": {
        "field": "partnerId"
      },
      "aggs": {
        "totalValue": {
          "sum": {
            "field": "value"
          }
        }
      }
    }
  }
}

Sample Result (agg only):

"aggregations": {
    "partner": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": 9876,
          "doc_count": 3,
          "totalValue": {
            "value": 3704.68017578125
          }
        },
        {
          "key": 9878,
          "doc_count": 2,
          "totalValue": {
            "value": 2454.1201171875
          }
        }
      ]
    }

In the result above key is partnerId, doc_count is count and totalValue.value is totalValue of your expected result.

Upvotes: 5

Related Questions