# Using NOT in a nested filter **URL:** \ **Category:** Elasticsearch\ **Created:** [January 9, 2014, 7:05pm UTC](https://discuss.elastic.co/t/using-not-in-a-nested-filter/15181 "2014-01-09T19:05:40Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Nathan\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nathan_2/32/1875_2.png) [@Nathan\_2](https://discuss.elastic.co/u/Nathan_2)\ **Post date:** [January 9, 2014, 7:05pm UTC](https://discuss.elastic.co/t/using-not-in-a-nested-filter/15181/1 "2014-01-09T19:05:40Z")
I am having trouble with a filter. I have items in my index, with nested "ratings" curl -XPOST "[http://localhost:9200/nestedfilters/item/\_mapping](http://localhost:9200/nestedfilters/item/_mapping)" -d ' { "item" : { "properties" : { "description" : { "type" : "string" }, "ratings" : { "type" : "nested", "properties" : { "rater\_username" : { "type" : "string", "index" : "not\_analyzed" }, "rating" : { "type" : "integer", "index" : "not\_analyzed" } } } } } } ' I want to be able to find items where a certain user has not rated the item. I have tried using NOT, but it finds anything rated by anybody else, regardless of whether the specific user has rated it. I can't seem to figure out how to use a MISSING filter either. Here is what I have tried: curl -XPOST "[http://localhost:9200/nestedfilters/item/\_search?pretty=true](http://localhost:9200/nestedfilters/item/_search?pretty=true)" -d ' { "query" : { "match\_all" : {} }, "filter" : { "nested" : { "path" : "ratings", "filter" : { "not" : { "term" : { "ratings.rater\_username" : "user1" } } } } } } ' and curl -XPOST "[http://localhost:9200/nestedfilters/item/\_search?pretty=true](http://localhost:9200/nestedfilters/item/_search?pretty=true)" -d ' { "query" : { "match\_all" : {} }, "filter" : { "nested" : { "path" : "ratings", "filter" : { "and" : [{ "term" : { "ratings.rater\_username" : "user1" } },{ "missing" : { "field" : "ratings.rating" } }] } } } } ' Here is the gist with a full example: [https://gist.github.com/nathanmoon/8339950](https://gist.github.com/nathanmoon/8339950). Is there another way I haven't thought of to craft a filter like this? Or do I need to index my data differently to support this type of filtering? Thanks for any help! Nathan -- 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/fc6ce18d-2923-4b87-b992-fc81a72c69a4%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/fc6ce18d-2923-4b87-b992-fc81a72c69a4%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Sloan\_Ahrens](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sloan_ahrens/32/1828_2.png) [@Sloan\_Ahrens](https://discuss.elastic.co/u/Sloan_Ahrens)\ **Post date:** [January 9, 2014, 8:10pm UTC](https://discuss.elastic.co/t/using-not-in-a-nested-filter/15181/2 "2014-01-09T20:10:37Z")
You were close. You just had the "nested" and "not" filters in the wrong order, basically. Your (first) query says "return items that have a rating with 'ratings.rater\_username' not equal to 'user1'". And so you get the first item, since it meets that requirement. What you really want to say is "return items for which all ratings have 'ratings.rater\_username' not equal to 'user1'". Here is the query you want: curl -XPOST "[http://localhost:9200/nestedfilters/item/\_search](http://localhost:9200/nestedfilters/item/_search)" -d' { "query": { "match\_all": {} }, "filter": { "not": { "nested": { "path": "ratings", "filter": { "term": { "ratings.rater\_username": "user1" } } } } } }' Here is a runnable example you can play with (you will need ES installed and running at localhost:9200, or supply another endpoint): [http://sense.qbox.io/gist/289ceb80480db8b6574d5f879358e50c97aaf5da](http://sense.qbox.io/gist/289ceb80480db8b6574d5f879358e50c97aaf5da) ---
**Author:** ![Nathan\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nathan_2/32/1875_2.png) [@Nathan\_2](https://discuss.elastic.co/u/Nathan_2)\ **Post date:** [January 9, 2014, 8:17pm UTC](https://discuss.elastic.co/t/using-not-in-a-nested-filter/15181/3 "2014-01-09T20:17:15Z")
Oh right. That should have been obvious. It seems to be working great that way. Thanks! Nathan On Jan 9, 2014, at 1:10 PM, Sloan Ahrens [sloan@stacksearch.com](mailto:sloan@stacksearch.com) wrote: > You were close. You just had the "nested" and "not" filters in the wrong > order, basically. > > Your (first) query says "return items that have a rating with > 'ratings.rater\_username' not equal to 'user1'". And so you get the first > item, since it meets that requirement. > > What you really want to say is "return items for which all ratings have > 'ratings.rater\_username' not equal to 'user1'". Here is the query you want: > > curl -XPOST "[http://localhost:9200/nestedfilters/item/\_search](http://localhost:9200/nestedfilters/item/_search)" -d' > { > "query": { > "match\_all": {} > }, > "filter": { > "not": { > "nested": { > "path": "ratings", > "filter": { > "term": { > "ratings.rater\_username": "user1" > } > } > } > } > } > }' > > Here is a runnable example you can play with (you will need ES installed and > running at localhost:9200, or supply another endpoint): > [http://sense.qbox.io/gist/289ceb80480db8b6574d5f879358e50c97aaf5da](http://sense.qbox.io/gist/289ceb80480db8b6574d5f879358e50c97aaf5da) > > * * * > > ## Co-Founder and CTO, StackSearch, Inc. Hosted Elasticsearch at [http://qbox.io](http://qbox.io) > > View this message in context: [http://elasticsearch-users.115913.n3.nabble.com/Using-NOT-in-a-nested-filter-tp4047349p4047353.html](http://elasticsearch-users.115913.n3.nabble.com/Using-NOT-in-a-nested-filter-tp4047349p4047353.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). > > -- > You received this message because you are subscribed to a topic in the Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit [https://groups.google.com/d/topic/elasticsearch/7yWbMCYmAFw/unsubscribe](https://groups.google.com/d/topic/elasticsearch/7yWbMCYmAFw/unsubscribe). > To unsubscribe from this group and all its topics, 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/1389298238074-4047353.post%40n3.nabble.com](https://groups.google.com/d/msgid/elasticsearch/1389298238074-4047353.post%40n3.nabble.com). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- 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/128FBB57-B971-4D1C-A3A6-E4F5A3F2BC3D%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/128FBB57-B971-4D1C-A3A6-E4F5A3F2BC3D%40gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**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:57am UTC](https://discuss.elastic.co/t/using-not-in-a-nested-filter/15181/4 "2017-07-06T01:57:36Z")