# Filter records having count =0 in aggregation

**URL:** https://discuss.elastic.co/t/filter-records-having-count-0-in-aggregation/344679
**Category:** Elasticsearch
**Tags:** eql-elastic-query-language
**Created:** [October 9, 2023, 3:24pm UTC](https://discuss.elastic.co/t/filter-records-having-count-0-in-aggregation/344679 "2023-10-09T15:24:30Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![star42](https://avatars.discourse-cdn.com/v4/letter/s/b782af/32.png) [@star42](https://discuss.elastic.co/u/star42)
#### Post date: [October 9, 2023, 3:24pm UTC](https://discuss.elastic.co/t/filter-records-having-count-0-in-aggregation/344679/1 "2023-10-09T15:24:30Z")

</div>

Hi,

I have a requirement to fetch categories having zero count

Query :

GET /category-sale-\*/\_search?size=100&filter\_path=aggregations  
{  
"query": {  
"bool": {  
"filter": {  
"bool": {  
"must\_not": [  
{  
"bool": {  
"should": [  
{  
"match\_phrase": {  
"region\_cd": "6"  
}  
},  
{  
"match\_phrase": {  
"region\_cd": "65"  
}  
},  
{  
"match\_phrase": {  
"region\_cd": "62"  
}  
}  
],  
"minimum\_should\_match": 1  
}  
}  
]  
}  
}  
}  
},  
"aggs": {  
"Data\_val": {  
"filter": {  
"range": {  
"@timestamp": {  
"gte":"now-2h",  
"lte":"now"  
}  
}  
},  
"aggs": {  
"NAME": {  
"terms": {  
"field": "catgy\_id",  
"size": 10000,  
"min\_doc\_count": 0,  
"order": {  
"\_count": "asc"  
}  
}

```
  }
  }
}

```

}  
}

Output:-

{  
"aggregations": {  
"Data\_val": {  
"doc\_count": 21027297,  
"NAME": {  
"doc\_count\_error\_upper\_bound": 0,  
"sum\_other\_doc\_count": 0,  
"buckets": [  
{  
"key": "1302",  
"doc\_count": 0  
},  
{  
"key": "353",  
"doc\_count": 0  
},  
{  
"key": "301",  
"doc\_count": 0  
},  
{  
"key": "221",  
"doc\_count": 0  
},  
....  
}

The output has both records having doc\_count 0 and non-zero values. Requirement is to fetch the category having only zero

Used the below query

GET /category-sale-\*/\_search?size=100&filter\_path=aggregations  
{  
"query": {  
"bool": {  
"filter": {  
"bool": {  
"must\_not": [  
{  
"bool": {  
"should": [  
{  
"match\_phrase": {  
"region\_cd": "6"  
}  
},  
{  
"match\_phrase": {  
"region\_cd": "65"  
}  
},  
{  
"match\_phrase": {  
"region\_cd": "62"  
}  
}  
],  
"minimum\_should\_match": 1  
}  
}  
]  
}  
}  
}  
},  
"aggs": {  
"Data\_val": {  
"filter": {  
"range": {  
"@timestamp": {  
"gte":"now-2h",  
"lte":"now"  
}  
}  
},  
"aggs": {  
"NAME": {  
"terms": {  
"field": "catgy\_id",  
"size": 10000,  
"min\_doc\_count": 0,  
"order": {  
"\_count": "asc"  
}  
} ,  
"aggs":{  
"filter\_zero":{  
"bucket\_selector": {  
"buckets\_path": {  
"val":"NAME.buckets"  
},  
"script": "val.\_count==0"  
}  
}  
}  
}  
}  
}  
}  
}

but getting error ""type": "action\_request\_validation\_exception",  
"reason": "Validation Failed: 1: No aggregation found for path [NAME.buckets];"

How to achieve this?

---

<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: [November 6, 2023, 3:24pm UTC](https://discuss.elastic.co/t/filter-records-having-count-0-in-aggregation/344679/2 "2023-11-06T15:24:53Z")

</div>

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