# Filter empty terms buckets after aggregation

**URL:** <https://discuss.elastic.co/t/filter-empty-terms-buckets-after-aggregation/127039>\
**Category:** Elasticsearch\
**Created:** [April 6, 2018, 7:03am UTC](https://discuss.elastic.co/t/filter-empty-terms-buckets-after-aggregation/127039 "2018-04-06T07:03:01Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![aurimas](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@aurimas](https://discuss.elastic.co/u/aurimas)\
**Post date:** [April 6, 2018, 7:03am UTC](https://discuss.elastic.co/t/filter-empty-terms-buckets-after-aggregation/127039/1 "2018-04-06T07:03:01Z")

</div>

I am trying to run a query which returns only network device and interface which have specific utilization.  
I am using **bucket\_selector** to filter out value aggregations which does not meat criteria and it almost gives me what I want, but terms (keys) are still returned with empty buckets see output below:

Aggregation:

```
"aggs": {
	"DBF_Device": {
		"terms": {
			"field": "tag.agent_host",
			"size": 1000,
			"order": {
				"_term": "desc"
			}
		},
		"aggs": {
			"DBF_Interface": {
				"terms": {
					"field": "tag.ifDescr",
					"size": 1000,
					"order": {
						"_term": "desc"
					}
				},
				"aggs": {
					"DBF_Metric": {
						"date_histogram": {
							"interval": "5m",
							"field": "@timestamp"
						},
						"aggs": {
							"DBF_Speed": {
								"max": {
									"field": "interface.ifHighSpeed",
									"script": {
										"inline": "_value*1000000"
									}
								}
							},
							"DBF_Metric_AVG": {
								"avg": {
									"field": "interface.ifHCInOctets",
									"script": {
										"inline": "(_value*8)/300"
									}
								}
							},
							"DBF_Metric_DER": {
								"derivative": {
									"buckets_path": "DBF_Metric_AVG"
								}
							},
							"DBF_Metric_Percent": {
								"bucket_script": {
									"buckets_path": {
										"my_var1": "DBF_Metric_DER",
										"my_var2": "DBF_Speed"
									},
									"script": "(params.my_var1 * 100)/ params.my_var2"
								}
							},
							"DBF_Bucket_filter": {
								"bucket_selector": {
									"buckets_path": {
										"my_var3": "DBF_Metric_Percent"
									},
									"script": "10 < (params.my_var3 ?: 0) && (params.my_var3 ?: 0)< 100"
								}
							}
						}
					}
				}
			}
		}
	}
}

```

Output:

```
            {
                "key": "ROUTER-1.domain.net",
                "doc_count": 28,
                "DBF_Interface": {
                    "doc_count_error_upper_bound": 0,
                    "sum_other_doc_count": 0,
                    "buckets": [
                        {
                            "key": "TenGigabitEthernet1/1/4",
                            "doc_count": 14,
                            "DBF_Metric": {
                                "buckets": []
                            }
                        },
                        {
                            "key": "TenGigabitEthernet1/1/3",
                            "doc_count": 14,
                            "DBF_Metric": {
                                "buckets": []
                            }
                        }
                    ]
                }
            },
            {
                "key": "ROUTER-2.domain.net",
                "doc_count": 42,
                "DBF_Interface": {
                    "doc_count_error_upper_bound": 0,
                    "sum_other_doc_count": 0,
                    "buckets": [
                        {
                            "key": "GigabitEthernet0/0/2",
                            "doc_count": 14,
                            "DBF_Metric": {
                                "buckets": []
                            }
                        },
                        {
                            "key": "GigabitEthernet0/0/1",
                            "doc_count": 14,
                            "DBF_Metric": {
                                "buckets": []
                            }
                        },
                        {
                            "key": "GigabitEthernet0/0/0",
                            "doc_count": 14,
                            "DBF_Metric": {
                                "buckets": [
                                    {
                                        "key_as_string": "2018-04-04T13:55:00.000Z",
                                        "key": 1522850100000,
                                        "doc_count": 10,
                                        "DBF_Speed": {
                                            "value": 100000000
                                        },
                                        "DBF_Metric_AVG": {
                                            "value": 16481198003.642666
                                        },
                                        "DBF_Metric_DER": {
                                            "value": 19436389.002666473
                                        },
                                        "DBF_Metric_Percent": {
                                            "value": 19.436389002666473
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            },

```

From the output you can see that only [ROUTER-2.domain.net](http://ROUTER-2.domain.net) GigabitEthernet0/0/0 meets criteria in bucket\_selector and DBF\_Metrics aggregation values are returned, which is good.  
But other interfaces on same device does not meet criteria and all interfaces on [ROUTER-1.domain.net](http://ROUTER-1.domain.net) also does not meat criteria but they are still listed but with empty DBF\_Metrics bucket.

Any idea how can I also filter out terms buckets which have metrics aggregation buckets empty?

---

<div class="post-metadata">

**Author:** ![Sravanthi\_N\_S\_CH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sravanthi_n_s_ch/32/50473_2.png) [@Sravanthi\_N\_S\_CH](https://discuss.elastic.co/u/Sravanthi_N_S_CH)\
**Post date:** [April 6, 2018, 7:28am UTC](https://discuss.elastic.co/t/filter-empty-terms-buckets-after-aggregation/127039/2 "2018-04-06T07:28:39Z")

</div>

Did you try using min\_doc\_count. Please refer [documentation](https://www.elastic.co/guide/en/elasticsearch/guide/current/_returning_empty_buckets.html)

---

<div class="post-metadata">

**Author:** ![aurimas](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@aurimas](https://discuss.elastic.co/u/aurimas)\
**Post date:** [April 6, 2018, 8:01am UTC](https://discuss.elastic.co/t/filter-empty-terms-buckets-after-aggregation/127039/3 "2018-04-06T08:01:29Z")

</div>

Yes, I had a thought about it but, doc count matters before aggregations as it works on "raw" data, and as you can see in my output above for for empty terms buckets: "doc\_count": 14.  
I need something that could filter based on aggregation results- buckets.

---

<div class="post-metadata">

**Author:** ![Sravanthi\_N\_S\_CH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sravanthi_n_s_ch/32/50473_2.png) [@Sravanthi\_N\_S\_CH](https://discuss.elastic.co/u/Sravanthi_N_S_CH)\
**Post date:** [April 6, 2018, 8:38am UTC](https://discuss.elastic.co/t/filter-empty-terms-buckets-after-aggregation/127039/4 "2018-04-06T08:38:45Z")

</div>

Sorry, I missed that..  
How about this [thread](https://stackoverflow.com/questions/37593366/filter-empty-buckets-from-aggregation-results). See if this is helpful

---

<div class="post-metadata">

**Author:** ![aurimas](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@aurimas](https://discuss.elastic.co/u/aurimas)\
**Post date:** [April 6, 2018, 9:00am UTC](https://discuss.elastic.co/t/filter-empty-terms-buckets-after-aggregation/127039/5 "2018-04-06T09:00:54Z")

</div>

Yes I saw that also before and tried it out, but it does not seem to work. As in example I have added additional bucket\_selector (MIN\_BUCKET\_SELECTOR) at the same level as the previous one:

```
							"DBF_Bucket_filter": {
								"bucket_selector": {
									"buckets_path": {
										"my_var3": "DBF_Metric_Percent"
									},
									"script": "10 < (params.my_var3 ?: 0) && (params.my_var3 ?: 0)< 100"
								}
							},
							"MIN_BUCKET_SELECTOR": {
								"bucket_selector": {
									"buckets_path": {
										"count": "DBF_Metric._bucket_count"
									},
									"script": {
										"inline": "params.count != 0"
									}
								}
							}

```

But I get error:

```
{
    "took": 341,
    "timed_out": false,
    "_shards": {
        "total": 940,
        "successful": 912,
        "skipped": 912,
        "failed": 28,
        "failures": [
            {
                "shard": 0,
                "index": "test-index--2018.04.04",
                "node": "lGIeqEb9SPeqBB32oBHL_g",
                "reason": {
                    "type": "illegal_argument_exception",
                    "reason": "No aggregation found for path [DBF_Interface._bucket_count]"
                }
            }
        ]
    },
    "hits": {
        "total": 0,
        "max_score": 0,
        "hits": []
    }
}

```

There are to possibilities why it is not working:  
either I make addressing of variable wrong  
or  
it is not suppose to work because bucket\_selector only filters withing aggregation it is placed in, and DBF\_Metric and DBF\_Interface are higher level aggregations and is not affected by aggregations done in their child agregations.

---

<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:** [May 4, 2018, 9:01am UTC](https://discuss.elastic.co/t/filter-empty-terms-buckets-after-aggregation/127039/6 "2018-05-04T09:01:00Z")

</div>

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