# Aggregating In Elastic Search

**URL:** <https://discuss.elastic.co/t/aggregating-in-elastic-search/341762>\
**Category:** Elasticsearch\
**Created:** [August 27, 2023, 7:17pm UTC](https://discuss.elastic.co/t/aggregating-in-elastic-search/341762 "2023-08-27T19:17:09Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Harinder\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/harinder_singh/32/106262_2.png) [@Harinder\_Singh](https://discuss.elastic.co/u/Harinder_Singh)\
**Post date:** [August 27, 2023, 7:17pm UTC](https://discuss.elastic.co/t/aggregating-in-elastic-search/341762/1 "2023-08-27T19:17:09Z")

</div>

Hi @leandrojmp ,

I have a below requirement, where I need to perform aggregation based on certain fields of Elasticsearch. Documents indexed are as below

```auto
PUT rollup-index/_doc/1
{
  "environment" : "preview",
  "personalizedSignal" : "category:appliance",
  "count" : 1000,
  "analyticsId" : "abcd",
  "eventType" : "impression",
  "timestamp" : 1692343020000
    
}

PUT rollup-index/_doc/2
{
  "environment" : "preview",
  "personalizedSignal" : "category:appliance",
  "count" : 10,
  "analyticsId" : "abcd",
  "eventType" : "click",
  "timestamp" : 1692343020000
    
}

PUT rollup-index/_doc/3
{
  "environment" : "preview",
  "personalizedSignal" : "category:appliance",
  "count" : 1000,
  "analyticsId" : "abcd",
  "eventType" : "impression",
  "timestamp" : 1692417540000
    
}

PUT rollup-index/_doc/4
{
  "environment" : "preview",
  "personalizedSignal" : "category:appliance",
  "count" : 10,
  "analyticsId" : "abcd",
  "eventType" : "click",
  "timestamp" : 1692417540000
    
}

PUT rollup-index/_doc/5
{
  "environment" : "preview",
  "personalizedSignal" : "category:appliance",
  "count" : 1000,
  "analyticsId" : "abcd",
  "eventType" : "impression",
  "timestamp" : 1692686160000
    
}

PUT rollup-index/_doc/6
{
  "environment" : "preview",
  "personalizedSignal" : "category:appliance",
  "count" : 10,
  "analyticsId" : "abcd",
  "eventType" : "click",
  "timestamp" : 1692686160000
    
}

PUT rollup-index/_doc/7
{
  "environment" : "preview",
  "personalizedSignal" : "category:kitchen",
  "count" : 1000,
  "analyticsId" : "abcd",
  "eventType" : "impression",
  "timestamp" : 1692686160000
    
}

PUT rollup-index/_doc/8
{
  "environment" : "preview",
  "personalizedSignal" : "category:kitchen",
  "count" : 10,
  "analyticsId" : "abcd",
  "eventType" : "click",
  "timestamp" : 1692686160000
    
}

```

Sample Query

```auto
GET rollup-index/_search
{
  "size": 0,
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "filter": [
              {
                "range": {
                  "timestamp": {
                    "gte": 1692343020000
                  }
                }
              }
            ],
            "must": [
              {
                "bool": {
                  "should": [
                    {
                      "bool": {
                        "must": [
                          {
                            "match_phrase": {
                              "analyticsId": "abcd"
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "bySignal": {
      "terms": {
        "field": "personalizedSignal.keyword"
      }
    }
  }
}

```

It produces output like below

```auto
  "aggregations" : {
    "bySignal" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "category:appliance",
          "doc_count" : 6
        },
        {
          "key" : "category:kitchen",
          "doc_count" : 2
        }
      ]
    }
  }

```

My requirement is to get a output after performing a second aggregation something like below (maynot be the exaxt format in which elastic returns)

```auto
  {
  "aggregations": {
    "bySignal": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "category:appliance",
          "buckets": [
            {
              "key": "impression",
              "count": 3000
            },
            {
              "key": "click",
              "count": 30
            }
          ]
        },
        {
          "key": "category:kitchen",
          "buckets": [
            {
              "key": "impression",
              "count": 1000
            },
            {
              "key": "click",
              "count": 10
            }
          ]
        }
      ]
    }
  }
}

```

First , the requirement is group by personalizedSignal, followed by sum of impression and click variable separately but again a full sum.

---

<div class="post-metadata">

**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\
**Post date:** [August 27, 2023, 11:45pm UTC](https://discuss.elastic.co/t/aggregating-in-elastic-search/341762/2 "2023-08-27T23:45:36Z")

</div>

Hi @Harinder_Singh

Try this using [Filter Aggs](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html).

```auto
{
   "size":0,
   "query":{
      "bool":{
         "should":[
            {
               "bool":{
                  "filter":[
                     {
                        "range":{
                           "timestamp":{
                              "gte":1692343020000
                           }
                        }
                     }
                  ],
                  "must":[
                     {
                        "bool":{
                           "should":[
                              {
                                 "bool":{
                                    "must":[
                                       {
                                          "match_phrase":{
                                             "analyticsId":"abcd"
                                          }
                                       }
                                    ]
                                 }
                              }
                           ]
                        }
                     }
                  ]
               }
            }
         ]
      }
   },
   "aggs":{
      "bySignal":{
         "terms":{
            "field":"personalizedSignal.keyword"
         },
         "aggs":{
            "impression":{
               "filter":{
                  "term":{
                     "eventType.keyword":"impression"
                  }
               },
               "aggs":{
                  "sum_impression":{
                     "sum":{
                        "field":"count"
                     }
                  }
               }
            },
            "click":{
               "filter":{
                  "term":{
                     "eventType.keyword":"click"
                  }
               },
               "aggs":{
                  "sum_impression":{
                     "sum":{
                        "field":"count"
                     }
                  }
               }
            }
         }
      }
   }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 24, 2023, 11:46pm UTC](https://discuss.elastic.co/t/aggregating-in-elastic-search/341762/3 "2023-09-24T23:46:07Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.