# Filtering nested aggregates

**URL:** <https://discuss.elastic.co/t/filtering-nested-aggregates/17436>\
**Category:** Elasticsearch\
**Created:** [May 9, 2014, 5:48pm UTC](https://discuss.elastic.co/t/filtering-nested-aggregates/17436 "2014-05-09T17:48:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ary\_Borenszweig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ary_borenszweig/32/1571_2.png) [@Ary\_Borenszweig](https://discuss.elastic.co/u/Ary_Borenszweig)\
**Post date:** [May 9, 2014, 5:48pm UTC](https://discuss.elastic.co/t/filtering-nested-aggregates/17436/1 "2014-05-09T17:48:50Z")

</div>

Hi,

I have an index where I need to store medical test results. A test result  
can talk about many conditions and their results: for example, Tuberculosis  
=\> positive, Flu =\> negative. So I modeled my index like this:

curl -XPUT "[http://localhost:9200/test\_results/](http://localhost:9200/test_results/)" -d'  
{  
"mappings": {  
"result": {  
"properties": {  
"data": {  
"type": "nested",  
"properties": {  
"condition": {"type": "string"},  
"result": {"type": "string"}  
}  
}  
}  
}  
}  
}'

I insert one test result with Tuberculosis =\> positive, Flu =\> negative:

curl -XPOST "[http://localhost:9200/test\_results/\_bulk](http://localhost:9200/test_results/_bulk)" -d'  
{"index":{"\_index":"test\_results","\_type":"result"}}  
{"data": [{"condition": "Tuberculosis", "result": "positive"},  
{"condition": "FLU", "result": "negative"}]}  
'

Then, one of the queries I need to do is this one: for Tuberculosis, give  
me how many positives you have and how many negatives you have (basically:  
filter by data.condition and group by data.result). So I tried this query:

curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
"size": 0,  
"query": {  
"nested": {  
"path": "data",  
"query": {  
"match": {  
"data.condition": "Tuberculosis"  
}  
}  
}  
},  
"aggregations": {  
"data": {  
"nested": {  
"path": "data"  
},  
"aggregations": {  
"result": {  
"terms": {  
"field": "data.result"  
}  
}  
}  
}  
}  
}  
'

However, the above gives me this result:

"aggregations" : {  
"data" : {  
"doc\_count" : 2,  
"result" : {  
"buckets" : [ {  
"key" : "negative",  
"doc\_count" : 1  
}, {  
"key" : "positive",  
"doc\_count" : 1  
} ]  
}  
}  
}

That is, it gives me one negative result and one positive result. That's  
because the document has one positive and negative, and it's not discarding  
the one that has "Flu".

I see in the documentation there's a "filter" aggregate. I tried using it  
in many ways:

1. With term on "data.condition":

curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
"size": 0,  
"query": {  
"nested": {  
"path": "data",  
"query": {  
"match": {  
"data.condition": "Tuberculosis"  
}  
}  
}  
},  
"aggregations": {  
"data": {  
"nested": {  
"path": "data"  
},  
"aggregations": {  
"filtered\_result": {  
"filter": {  
"term": { "data.condition" : "Tuberculosis" }  
},  
"aggregations" : {  
"result": {  
"terms": {  
"field": "data.result"  
}  
}  
}  
}  
}  
}  
}  
}  
'

1. With term on "condition":

curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
"size": 0,  
"query": {  
"nested": {  
"path": "data",  
"query": {  
"match": {  
"data.condition": "Tuberculosis"  
}  
}  
}  
},  
"aggregations": {  
"data": {  
"nested": {  
"path": "data"  
},  
"aggregations": {  
"filtered\_result": {  
"filter": {  
"term": { "condition" : "Tuberculosis" }  
},  
"aggregations" : {  
"result": {  
"terms": {  
"field": "data.result"  
}  
}  
}  
}  
}  
}  
}  
}  
'

1. With nested:

curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
"size": 0,  
"query": {  
"nested": {  
"path": "data",  
"query": {  
"match": {  
"data.condition": "Tuberculosis"  
}  
}  
}  
},  
"aggregations": {  
"data": {  
"nested": {  
"path": "data"  
},  
"aggregations": {  
"filtered\_result": {  
"filter": {  
"nested": {  
"path": "data",  
"filter": {  
"term": { "data.condition": "Tuberculosis" }  
}  
}  
},  
"aggregations" : {  
"result": {  
"terms": {  
"field": "data.result"  
}  
}  
}  
}  
}  
}  
}  
}  
'

but no luck: all of the above queries just give me:

"aggregations" : {  
"data" : {  
"doc\_count" : 2,  
"filtered\_result" : {  
"doc\_count" : 0,  
"result" : {  
"buckets" : []  
}  
}  
}  
}

Is there a way to do what I want?

Thanks,  
Ary

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/f5094888-6654-4d40-bf9b-d81ec1e5add4%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/f5094888-6654-4d40-bf9b-d81ec1e5add4%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Ary\_Borenszweig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ary_borenszweig/32/1571_2.png) [@Ary\_Borenszweig](https://discuss.elastic.co/u/Ary_Borenszweig)\
**Post date:** [May 12, 2014, 6:01pm UTC](https://discuss.elastic.co/t/filtering-nested-aggregates/17436/2 "2014-05-12T18:01:15Z")

</div>

A friend of mine made it work. It wasn't working because we were using a  
filter -\> term inside the nested aggregation with "Tuberculosis", but the  
analyzed value was "tuberculosis". Changing "Tuberculosis" to  
"tuberculosis" made it work. Also, repeating the first query (instead of  
using a filter) makes it work in the nested filter.

Here's one example:

curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
"size": 0,  
"query": {  
"nested": {  
"path": "data",  
"query": {  
"match": {  
"data.condition": "Tuberculosis"  
}  
}  
}  
},  
"aggregations": {  
"data": {  
"nested": {  
"path": "data"  
},  
"aggregations": {  
"filtered\_result": {  
"filter": {  
"query": {  
"match": {  
"condition": "Tuberculosis"  
}  
}  
},  
"aggregations" : {  
"result": {  
"terms": {  
"field": "data.result"  
}  
}  
}  
}  
}  
}  
}  
}  
'

On Friday, May 9, 2014 2:48:50 PM UTC-3, Ary Borenszweig wrote:

> Hi,
> 
> I have an index where I need to store medical test results. A test result  
> can talk about many conditions and their results: for example, Tuberculosis  
> =\> positive, Flu =\> negative. So I modeled my index like this:
> 
> curl -XPUT "[http://localhost:9200/test\_results/](http://localhost:9200/test_results/)" -d'  
> {  
> "mappings": {  
> "result": {  
> "properties": {  
> "data": {  
> "type": "nested",  
> "properties": {  
> "condition": {"type": "string"},  
> "result": {"type": "string"}  
> }  
> }  
> }  
> }  
> }  
> }'
> 
> I insert one test result with Tuberculosis =\> positive, Flu =\> negative:
> 
> curl -XPOST "[http://localhost:9200/test\_results/\_bulk](http://localhost:9200/test_results/_bulk)" -d'  
> {"index":{"\_index":"test\_results","\_type":"result"}}  
> {"data": [{"condition": "Tuberculosis", "result": "positive"},  
> {"condition": "FLU", "result": "negative"}]}  
> '
> 
> Then, one of the queries I need to do is this one: for Tuberculosis, give  
> me how many positives you have and how many negatives you have (basically:  
> filter by data.condition and group by data.result). So I tried this query:
> 
> curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
> "size": 0,  
> "query": {  
> "nested": {  
> "path": "data",  
> "query": {  
> "match": {  
> "data.condition": "Tuberculosis"  
> }  
> }  
> }  
> },  
> "aggregations": {  
> "data": {  
> "nested": {  
> "path": "data"  
> },  
> "aggregations": {  
> "result": {  
> "terms": {  
> "field": "data.result"  
> }  
> }  
> }  
> }  
> }  
> }  
> '
> 
> However, the above gives me this result:
> 
> "aggregations" : {  
> "data" : {  
> "doc\_count" : 2,  
> "result" : {  
> "buckets" : [ {  
> "key" : "negative",  
> "doc\_count" : 1  
> }, {  
> "key" : "positive",  
> "doc\_count" : 1  
> } ]  
> }  
> }  
> }
> 
> That is, it gives me one negative result and one positive result. That's  
> because the document has one positive and negative, and it's not discarding  
> the one that has "Flu".
> 
> I see in the documentation there's a "filter" aggregate. I tried using it  
> in many ways:
> 
> 1. With term on "data.condition":
> 
> curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
> "size": 0,  
> "query": {  
> "nested": {  
> "path": "data",  
> "query": {  
> "match": {  
> "data.condition": "Tuberculosis"  
> }  
> }  
> }  
> },  
> "aggregations": {  
> "data": {  
> "nested": {  
> "path": "data"  
> },  
> "aggregations": {  
> "filtered\_result": {  
> "filter": {  
> "term": { "data.condition" : "Tuberculosis" }  
> },  
> "aggregations" : {  
> "result": {  
> "terms": {  
> "field": "data.result"  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> '
> 
> 1. With term on "condition":
> 
> curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
> "size": 0,  
> "query": {  
> "nested": {  
> "path": "data",  
> "query": {  
> "match": {  
> "data.condition": "Tuberculosis"  
> }  
> }  
> }  
> },  
> "aggregations": {  
> "data": {  
> "nested": {  
> "path": "data"  
> },  
> "aggregations": {  
> "filtered\_result": {  
> "filter": {  
> "term": { "condition" : "Tuberculosis" }  
> },  
> "aggregations" : {  
> "result": {  
> "terms": {  
> "field": "data.result"  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> '
> 
> 1. With nested:
> 
> curl -XPOST "[http://localhost:9200/test\_results/\_search?pretty=true](http://localhost:9200/test_results/_search?pretty=true)" -d'{  
> "size": 0,  
> "query": {  
> "nested": {  
> "path": "data",  
> "query": {  
> "match": {  
> "data.condition": "Tuberculosis"  
> }  
> }  
> }  
> },  
> "aggregations": {  
> "data": {  
> "nested": {  
> "path": "data"  
> },  
> "aggregations": {  
> "filtered\_result": {  
> "filter": {  
> "nested": {  
> "path": "data",  
> "filter": {  
> "term": { "data.condition": "Tuberculosis" }  
> }  
> }  
> },  
> "aggregations" : {  
> "result": {  
> "terms": {  
> "field": "data.result"  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> '
> 
> but no luck: all of the above queries just give me:
> 
> "aggregations" : {  
> "data" : {  
> "doc\_count" : 2,  
> "filtered\_result" : {  
> "doc\_count" : 0,  
> "result" : {  
> "buckets" :   
> }  
> }  
> }  
> }
> 
> Is there a way to do what I want?
> 
> Thanks,  
> Ary

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/19846a1d-ea82-4097-859c-696591df1558%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/19846a1d-ea82-4097-859c-696591df1558%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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:** [July 6, 2017, 1:30am UTC](https://discuss.elastic.co/t/filtering-nested-aggregates/17436/3 "2017-07-06T01:30:02Z")

</div>