# Filtering aggregation results based on nested objects in ElasticSearch
**URL:** https://discuss.elastic.co/t/filtering-aggregation-results-based-on-nested-objects-in-elasticsearch/319313
**Category:** Elasticsearch
**Created:** [November 18, 2022, 3:11pm UTC](https://discuss.elastic.co/t/filtering-aggregation-results-based-on-nested-objects-in-elasticsearch/319313 "2022-11-18T15:11:59Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@kornikopic](https://discuss.elastic.co/u/kornikopic)
#### Post date: [November 18, 2022, 3:11pm UTC](https://discuss.elastic.co/t/filtering-aggregation-results-based-on-nested-objects-in-elasticsearch/319313/1 "2022-11-18T15:11:59Z")
In my database, I have 2 tables such as:
```nohighlight
+------------+ +-------------+
| Project | | Deliverable |
+------------+ +-------------+
| id +----------+ project_id |
| |1 *| id |
| | | category |
| | | status |
+------------+ +-------------+
```
For this example, the data are the folowing:
```nohighlight
+---------------+-----------+--------+---------------+
| Deliverable | Project | Status | Category (id) |
+---------------+-----------+--------+---------------+
| deliverable A | project A | OPEN | 86 |
| deliverable B | project A | CLOSED | 209 |
| deliverable C | project B | OPEN | 223 |
+---------------+-----------+--------+---------------+
```
In Elasticsearch, I have created this mapping (it's only a portion):
```json
{
"projects" : {
"mappings" : {
"properties" : {
"deliverables" : {
"type" : "nested",
"properties" : {
"category" : {
"properties" : {
"id" : {
"type" : "integer"
},
"name" : {
"type" : "text",
"fields" : {
"raw" : {
"type" : "keyword"
},
"suggest" : {
"type" : "completion",
"analyzer" : "simple",
"preserve_separators" : true,
"preserve_position_increments" : true,
"max_input_length" : 50
}
},
"analyzer" : "html_strip",
"fielddata" : true
},
}
},
}
},
"name" : {
"type" : "text"
}
}
}
}
}
```
I have created aggregation on `deliverables.category`:
```auto
GET /projects/_search
{
"aggs": {
"all_categories": {
"global": {},
"aggs": {
"categories": {
"filter": {
"bool": {
"must": [
{
"nested": {
"path": "deliverables",
"query": {
"range": {
"deliverables.expire_date": {
"gte": "2022-11-11"
}
}
}
}
},
{
"nested": {
"path": "deliverables",
"query": {
"term": {
"deliverables.status": "OPEN"
}
}
}
}
]
}
},
"aggs": {
"categories": {
"nested": {
"path": "deliverables"
},
"aggs": {
"categories": {
"terms": {
"field": "deliverables.category.id"
}
}
}
}
}
}
}
}
}
}
```
Which gives me this result:
```auto
"aggregations" : {
"all_categories" : {
"doc_count" : 2,
"categories" : {
"doc_count" : 2,
"categories" : {
"doc_count" : 3,
"categories" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 86,
"doc_count" : 1
},
{
"key" : 209,
"doc_count" : 1
},
{
"key" : 223,
"doc_count" : 1
}
]
}
}
}
}
}
```
I am expected to get 2 categories out of 3 because one deliverable is CLOSED. But from my understanding the result is based on the document Project not on the nested objects.
How can I get the result based on the nested object so I can get the 2 categories?
Please advise. Thank you.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 16, 2022, 3:12pm UTC](https://discuss.elastic.co/t/filtering-aggregation-results-based-on-nested-objects-in-elasticsearch/319313/2 "2022-12-16T15:12:23Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.