Aggregating In Elastic Search

Elastic Stack Elasticsearch
1

Hi @leandrojmp ,

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

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

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

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

  {
  "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.

2

Hi @Harinder_Singh

Try this using Filter Aggs.

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

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