Elastic query with filters


#1

Hi, Is it possible to exclude the zero count records in a query?
for example the below is the query

    {
   "size":0,
   "query":{
      "bool":{
         "must":[
            {
               "match_all":{

               }
            },
            {
               "range":{
                  "@timestamp":{
                     "gte":1538081958484,
                     "lte":1538082858484,
                     "format":"epoch_millis"
                  }
               }
            }
         ],
         "must_not":[

         ]
      }
   },
   "_source":{
      "excludes":[

      ]
   },
   "aggs":{
      "2":{
         "filters":{
            "filters":{
               "EU":{
                  "query_string":{
                     "query":"beat.hostname:f*",
                     "analyze_wildcard":true
                  }
               },
               "SA":{
                  "query_string":{
                     "query":"beat.hostname:z*",
                     "analyze_wildcard":true
                  }
               }
            }
         }
      }
   }
}

this gives the output as

{
   "responses":[
      {
         "took":6,
         "timed_out":false,
         "_shards":{
            "total":4,
            "successful":4,
            "skipped":0,
            "failed":0
         },
         "hits":{
            "total":57251,
            "max_score":0.0,
            "hits":[

            ]
         },
         "aggregations":{
            "2":{
               "buckets":{
                  "EU":{
                     "doc_count":19385
                  },
                  "SA":{
                     "doc_count":0
                  }
               }
            }
         },
         "status":200
      }
   ]
}

Since this is consumed by the kibana watcher, I would like the watcher to show only EU in the subject line. The actual query has one more level. Is it possible to not return the buckets with zero doc_counts?


(Abdon Pijpelink) #2

You can use a bucket selector pipeline aggregation to remove all buckets with a doc_count of 0. Your request would become:

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {}
        },
        {
          "range": {
            "@timestamp": {
              "gte": 1538081958484,
              "lte": 1538082858484,
              "format": "epoch_millis"
            }
          }
        }
      ],
      "must_not": []
    }
  },
  "_source": {
    "excludes": []
  },
  "aggs": {
    "2": {
      "filters": {
        "filters": {
          "EU": {
            "query_string": {
              "query": "beat.hostname:f*",
              "analyze_wildcard": true
            }
          },
          "SA": {
            "query_string": {
              "query": "beat.hostname:z*",
              "analyze_wildcard": true
            }
          }
        }
      },
      "aggs": {
        "my_bucket_selector": {
          "bucket_selector": {
            "buckets_path": {
              "count": "_count"
            },
            "script": "params.count > 0"
          }
        }
      }
    }
  }
}

#3

Thank you @abdon :+1:t4:, this was just perfect, I had missed this option completely.


(system) #4

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