# Elastic Search query that matches all elements in nested document **URL:** \ **Category:** Elasticsearch\ **Created:** [June 16, 2022, 11:26am UTC](https://discuss.elastic.co/t/elastic-search-query-that-matches-all-elements-in-nested-document/307395 "2022-06-16T11:26:42Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Zedson.Sagar](https://avatars.discourse-cdn.com/v4/letter/z/e9c0ed/32.png) [@Zedson.Sagar](https://discuss.elastic.co/u/Zedson.Sagar)\ **Post date:** [June 16, 2022, 11:26am UTC](https://discuss.elastic.co/t/elastic-search-query-that-matches-all-elements-in-nested-document/307395/1 "2022-06-16T11:26:42Z")
I have an index with nested document structure as below - ```auto { "firstindex": { "mappings": { "properties": { "Name": { "type": "text" }, "SecurityCodes": { "type": "nested", "properties": { "Description": { "type": "text" }, "Name": { "type": "text" } } } } } } } ``` Here is how the docs look like - ```auto { "Name": "Doc1", "SecurityCodes": [ { "Name": "Restricted", "Description": "" }, { "Name": "Unclassfied", "Description": "" } ] }, { "Name": "Doc2", "SecurityCodes": [ { "Name": "Restricted", "Description": "" }, { "Name": "Restricted", "Description": "" } ] }, { "Name": "Doc3", "SecurityCodes": [ { "Name": "Restricted", "Description": "" }, { "Name": "Unknown", "Description": "" } ] } ``` all i want to do is to write a query that returns documents where each securitycode matches the condition. for e.g. if the search string is Restricted (over secuirtycode.name) it should return doc2 but not doc1 and doc3 since each and every securitycode name matches Restricted under doc2 i tried this but it returns all the docs ```auto "query": { "nested": { "path": "SecurityCodes", "query": { "bool": { "must": [ { "term": { "SecurityCodes.Name": "Restricted" } } ] } } } } ``` i looked into the answer [here](https://stackoverflow.com/questions/64120458/es-query-to-match-all-elements-in-array) but that is not acceptable since it requires a field to exist on the index and we have lot of such nested docs in our index and having a copy feild for each one is not acceptable. can any one help me out please? ---
**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\ **Post date:** [June 16, 2022, 1:28pm UTC](https://discuss.elastic.co/t/elastic-search-query-that-matches-all-elements-in-nested-document/307395/2 "2022-06-16T13:28:17Z")
Hi @Zedson.Sagar . From what I understand you just want the doc where all the SecurityCodes is "SecurityCodes.Name": "Restricted". The query below only searches for "Restricted" and excludes who is "Unclassfied" and "Unknown". ```auto { "query": { "bool": { "must_not": [ { "nested": { "path": "SecurityCodes", "query": { "match": { "SecurityCodes.Name": { "query": "Unclassfied" } } } } }, { "nested": { "path": "SecurityCodes", "query": { "match": { "SecurityCodes.Name": { "query": "Unknown" } } } } } ], "must": [ { "nested": { "path": "SecurityCodes", "query": { "match": { "SecurityCodes.Name": { "query": "Restricted" } } } } } ] } } } ``` ---
**Author:** ![Zedson.Sagar](https://avatars.discourse-cdn.com/v4/letter/z/e9c0ed/32.png) [@Zedson.Sagar](https://discuss.elastic.co/u/Zedson.Sagar)\ **Post date:** [June 16, 2022, 1:53pm UTC](https://discuss.elastic.co/t/elastic-search-query-that-matches-all-elements-in-nested-document/307395/3 "2022-06-16T13:53:31Z")
Hi @RabBit_BR, Thanks for your reply, the solution you provided works if the values for security codes are fixed Unclassified, Restricted, Unknown. However, that is not the case, these values are dynamic and keep expanding. Is there a way i can use a double negation to extract only objects that doesn't have fields that are different to the given one? this is how we can handle in SQL database - Parent docs are in OBJECTS table SecurityCodes are in SECURITYCODES Table a PARENTSECURITYCODE table that relates both Parent and Security (PARENT can have any number of Security Codes) ```auto Select * from OBJECTS O1 Where NOT EXISTS ( SELECT * FROM PARENTSECURITYCODE PSC INNER JOIN SECURITYCODE SC ON SC.NAME = PSC.CODENAME WHERE O1.NAME = PSC.PARENTNAME AND NOT SC.NAME = 'Restricted') ``` We would want to address this scenario in Elastic as well. Is ther a way i can do this? ---
**Author:** ![Mikhail\_Khludnev](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mikhail_khludnev/32/59591_2.png) [@Mikhail\_Khludnev](https://discuss.elastic.co/u/Mikhail_Khludnev)\ **Post date:** [June 16, 2022, 8:38pm UTC](https://discuss.elastic.co/t/elastic-search-query-that-matches-all-elements-in-nested-document/307395/4 "2022-06-16T20:38:24Z")
```auto { "query": { "bool": { "must_not": [ // exclude those parents, which ... { "nested": { "path": "SecurityCodes", "query": { "bool": { "must": [ // .. has any child ... "exists": { "field": "SecurityCodes.Name" } ], "must_not": [ // ... but not Restricted { "match": { "SecurityCodes.Name": { "query": "Restricted" } } } ], } } ] } } } ``` Assuming that ES handles top pure negation query, it might do the thing. ---
**Author:** ![Zedson.Sagar](https://avatars.discourse-cdn.com/v4/letter/z/e9c0ed/32.png) [@Zedson.Sagar](https://discuss.elastic.co/u/Zedson.Sagar)\ **Post date:** [June 17, 2022, 5:22am UTC](https://discuss.elastic.co/t/elastic-search-query-that-matches-all-elements-in-nested-document/307395/5 "2022-06-17T05:22:33Z")
Thanks @Mikhail_Khludnev this works. Appreciate your help 🙂 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 15, 2022, 5:22am UTC](https://discuss.elastic.co/t/elastic-search-query-that-matches-all-elements-in-nested-document/307395/6 "2022-07-15T05:22:50Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.