# Execute a filter on nested document only if it exists **URL:** \ **Category:** Elasticsearch\ **Created:** [January 25, 2017, 2:47am UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720 "2017-01-25T02:47:07Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![pungent](https://avatars.discourse-cdn.com/v4/letter/p/a698b9/32.png) [@pungent](https://discuss.elastic.co/u/pungent)\ **Post date:** [January 25, 2017, 2:47am UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/1 "2017-01-25T02:47:07Z")
I am using ES 2.3 and have a query in which `filter` section looks as follows: ``` "filter": { "query": { "bool": { "must": [ { "nested": { "path": "employees", "query": { "bool": { "must": [ { "range": { "employees.max_age": { "lte": 50 } } }, { "range": { "employees.min_age": { "gte": 20 } } } ] } } } }, { "exists": { "field": "employees" } }, { #....other filter here based on root document, not on nested employee document } ] } } } } ``` I have a filter, where I check some conditions in the nested document "employees" in a bigger document called company, But I want to run this filter, only if "employees" object exists, as some of the document may not have that nested document at all. So I added , `{"exists": {"field": "employees"}}` but this doesn't seem to work. Any idea what change I should make to get it work? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 25, 2017, 9:10am UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/2 "2017-01-25T09:10:56Z")
> [@pungent](#): > > Any idea what change I should make to get it work? In what way is it broken? False positives or false negatives? Either way I don't think you need the "exists" clause here. The data model seems unusual here. If I read it right you have a `company` doc with a nested array of `employee` objects each of which seem to have a `min_age` and a `max_age` value rather than recording an actual `age` of the employee or a birthdate? FYI if that is the case and age ranges are what you record then this new feature coming in 5.2 may be of interest: [Numeric and Date Ranges...Just Another Brick in the Wall | Elastic Blog](https://www.elastic.co/blog/numeric-and-date-ranges-in-elasticsearch-just-another-brick-in-the-wall) ---
**Author:** ![pungent](https://avatars.discourse-cdn.com/v4/letter/p/a698b9/32.png) [@pungent](https://discuss.elastic.co/u/pungent)\ **Post date:** [January 25, 2017, 3:34pm UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/3 "2017-01-25T15:34:19Z")
@Mark_Harwood thanks for the suggestion. Regarding "data model seems unusual" - I have intentionally did not put my whole data model and actual field names here. So let's ignore that part. What I am looking for is - if company document doesn't have employee document, then return that company document, but if that document has `employee` nested document, then run the filter. Right now the issue is if a document doesn't have `employees` nested document, then that document do not get returned at all, because it tries to run the filter on a non-existing `employees` document. So what change I should make in order to ignore filter, if `employees` does not exists. It is something like this `if employee.exists { run filter } else { return company doc}` .. make sense? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 25, 2017, 3:49pm UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/4 "2017-01-25T15:49:23Z")
OK I think I got it. You need a top level OR - so empty or, has employees with what you want. That needs a `bool` with a `should` clause. Try this: ``` DELETE test PUT test { "settings": { "index": { "number_of_shards": 1 } }, "mappings": { "company": { "properties": { "name": { "type": "text" }, "employees":{ "type":"nested", "properties":{ "age":{ "type":"integer" } } } } } } } POST test/company/1 { "name":"no employees" } POST test/company/2 { "name":"Some employees", "employees":[ {"age":20} ] } GET test/company/_search { "query": { "bool": { "should": [ { "bool": { "must_not": [ { "nested": { "path": "employees", "query": { "exists": { "field": "employees.age" } } } } ] } }, { "nested": { "path": "employees", "query": { "match": { "employees.age": 20 } } } } ] } } } ``` ---
**Author:** ![pungent](https://avatars.discourse-cdn.com/v4/letter/p/a698b9/32.png) [@pungent](https://discuss.elastic.co/u/pungent)\ **Post date:** [January 25, 2017, 5:00pm UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/5 "2017-01-25T17:00:31Z")
@Mark_Harwood First of all, I appreciate your time to write up the solution end to end with an example. You made my day. Indeed your solution works, but you missed one key point. As I mentioned in my question that I would also have other filters on root document they would be must. `{ #....other filter here based on root document, not on nested employee document }` So suppose in your example, I added few more documents as follows: ```auto POST test/company/3 { "name":"Some employees", "employees":[ {"age":40} ] } POST test/company/4 { "name":"Some employees", "employees":[ {"age":30} ] } ``` I am interested only to grab documents where company name condition matches i.e. `company.name == "Some employees"`. Since You are using `should` on the top level, then it won't be possible. Because if I add this condition in the query, then it will also pull document where company name == "no employees". Got complex 🙂 ---
**Author:** ![pungent](https://avatars.discourse-cdn.com/v4/letter/p/a698b9/32.png) [@pungent](https://discuss.elastic.co/u/pungent)\ **Post date:** [January 25, 2017, 5:10pm UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/6 "2017-01-25T17:10:08Z")
@Mark_Harwood If you map to a sql analogy so this is what it would be `select * from company where (company.employee.age > 10 or company.employee = null) and company.name="some employee"` ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 25, 2017, 5:22pm UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/7 "2017-01-25T17:22:20Z")
> [@pungent](#): > > You made my day No problem - glad to be of help. > [@pungent](#): > > I am interested only to grab documents where company name condition matches OK - so you just nest my example `bool` query under a new top-level `bool` inside a `must` clause along with the mandatory name criteria so: ``` GET test/company/_search { "query": { "bool": { "must": [ { "match":{ "name":"employees" } }, { "bool": { "should": [ { "bool": { "must_not": [ { "nested": { "path": "employees", "query": { "exists": { "field": "employees.age" } } } } ] } }, { "nested": { "path": "employees", "query": { "match": { "employees.age": 20 } } } } ] } } ] } } } ``` ---
**Author:** ![pungent](https://avatars.discourse-cdn.com/v4/letter/p/a698b9/32.png) [@pungent](https://discuss.elastic.co/u/pungent)\ **Post date:** [January 25, 2017, 5:58pm UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/8 "2017-01-25T17:58:52Z")
Perfect, it worked 🙂 Thank you so much @Mark_Harwood . ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 22, 2017, 5:58pm UTC](https://discuss.elastic.co/t/execute-a-filter-on-nested-document-only-if-it-exists/72720/9 "2017-02-22T17:58:53Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.