# There's a nasty issue with filtering the aggregation results for nested documents

**URL:** <https://discuss.elastic.co/t/theres-a-nasty-issue-with-filtering-the-aggregation-results-for-nested-documents/359192>\
**Category:** Elasticsearch\
**Tags:** painless\
**Created:** [May 9, 2024, 1:33pm UTC](https://discuss.elastic.co/t/theres-a-nasty-issue-with-filtering-the-aggregation-results-for-nested-documents/359192 "2024-05-09T13:33:41Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![young\_karl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/young_karl/32/133815_2.png) [@young\_karl](https://discuss.elastic.co/u/young_karl)\
**Post date:** [May 9, 2024, 1:33pm UTC](https://discuss.elastic.co/t/theres-a-nasty-issue-with-filtering-the-aggregation-results-for-nested-documents/359192/1 "2024-05-09T13:33:41Z")

</div>

Regarding the following index:

1. For each document's nested sub-document `tradeRecords`, internally group them and count the number of `tradeRecords` where `tradeRecords.tradeDate` falls between [1647273600000, 1674144000000], denoted as `record_total`. This `record_total` is a result for each document.
2. Filter out all documents where `record_total` falls between [2,5].

json

Copy code

```auto
{
	"settings": {
		"number_of_shards": 1,
		"number_of_replicas": 0
	},
	"mappings": {
		"properties": {
			"id": {
				"type": "keyword"
			},
			"tradeRecords": {
				"type": "nested",
				"properties": {
					"tradeDate": {
						"type": "date"
					},
					"tradeId": {
						"type": "keyword"
					},
					"tradeType": {
						"type": "keyword"
					}
				}
			}
		}
	}
}

```

My query is throwing an error. Elasticsearch version is 7.13.4.

json

Copy code

```auto
{
	"size": 0,
	"aggs": {
		"documents": {
			"terms": {
				"field": "id",
				"size": 10
			},
			"aggs": {
				"nested_trade_records": {
					"nested": {
						"path": "tradeRecords"
					},
					"aggs": {
						"filtered_trade_records": {
							"filter": {
								"range": {
									"tradeRecords.tradeDate": {
										"gte": 1647273600000,
										"lte": 1674144000000
									}
								}
							}
						},
						"filtered_documents": {
							"bucket_selector": {
								"buckets_path": {
									"recordTotal": "filtered_trade_records._count"
								},
								"script": "params.recordTotal >= 2 && params.recordTotal <= 5"
							}
						}
					}
				}
			}
		}
	}
}

```

Detailed error message:

json

Copy code

```auto
{
    "error": {
        "root_cause": [],
        "type": "search_phase_execution_exception",
        "reason": "",
        "phase": "fetch",
        "grouped": true,
        "failed_shards": [],
        "caused_by": {
            "type": "class_cast_exception",
            "reason": "org.elasticsearch.search.aggregations.bucket.nested.InternalNested cannot be cast to org.elasticsearch.search.aggregations.InternalMultiBucketAggregation"
        }
    },
    "status": 500
}

```

---

<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:** [June 6, 2024, 1:33pm UTC](https://discuss.elastic.co/t/theres-a-nasty-issue-with-filtering-the-aggregation-results-for-nested-documents/359192/2 "2024-06-06T13:33:57Z")

</div>

Closed on June 6, 2024, 1:33pm UTC

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