# Must\_not does not work in the nested array

**URL:** <https://discuss.elastic.co/t/must-not-does-not-work-in-the-nested-array/374191>\
**Category:** Elasticsearch\
**Created:** [February 6, 2025, 6:13pm UTC](https://discuss.elastic.co/t/must-not-does-not-work-in-the-nested-array/374191 "2025-02-06T18:13:19Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![vinitp](https://avatars.discourse-cdn.com/v4/letter/v/46a35a/32.png) [@vinitp](https://discuss.elastic.co/u/vinitp)\
**Post date:** [February 6, 2025, 6:13pm UTC](https://discuss.elastic.co/t/must-not-does-not-work-in-the-nested-array/374191/1 "2025-02-06T18:13:19Z")

</div>

I have an application that generates Elasticsearch SQL dynamically based on the client's search criteria. The application then use the "/sql/translate" API to translate to the DSL query which it executes. The sample of the SQL looks like this:  
"select \* from hearing\_alias where hcAccountabilityOfficeCode = '550' AND hearingActionDetails.haaActionTypeCode = 'SU'  
AND hearingActionDetails.haaActionCatCode = 'A'  
AND hcStatusCode \<\> 'STS54'  
AND hearingActionDetails.haaPartyRespRcvdOn is null  
AND hearingActionDetails.haaPartyReplyRcvdOn is null  
order by hcCaseNumber desc limit 200"

This query gets translated into the following DSL:

{  
"size": 200,  
"query": {  
"bool": {  
"must": [  
{  
"term": {  
"hcAccountabilityOfficeCode.keyword": {  
"value": "550"  
}  
}  
},  
{  
"nested": {  
"query": {  
"term": {  
"hearingActionDetails.haaActionTypeCode.keyword": {  
"value": "SU"  
}  
}  
},  
"path": "hearingActionDetails",  
"ignore\_unmapped": false,  
"score\_mode": "none",  
"boost": 1.0  
}  
},  
{  
"nested": {  
"query": {  
"term": {  
"hearingActionDetails.haaActionCatCode.keyword": {  
"value": "A"  
}  
}  
},  
"path": "hearingActionDetails",  
"ignore\_unmapped": false,  
"score\_mode": "none",  
"boost": 1.0  
}  
},  
{  
"bool": {  
"must\_not": [  
{  
"term": {  
"hcStatusCode.keyword": {  
"value": "STS54"  
}  
}  
}  
],  
"boost": 1.0  
}  
},  
{  
"nested": {  
"query": {  
"bool": {  
"must\_not": [  
{  
"exists": {  
"field": "hearingActionDetails.haaPartyRespRcvdOn",  
"boost": 1.0  
}  
}  
],  
"boost": 1.0  
}  
},  
"path": "hearingActionDetails",  
"ignore\_unmapped": false,  
"score\_mode": "none",  
"boost": 1.0  
}  
},  
{  
"nested": {  
"query": {  
"bool": {  
"must\_not": [  
{  
"exists": {  
"field": "hearingActionDetails.haaPartyReplyRcvdOn",  
"boost": 1.0  
}  
}  
],  
"boost": 1.0  
}  
},  
"path": "hearingActionDetails",  
"ignore\_unmapped": false,  
"score\_mode": "none",  
"boost": 1.0  
}  
}  
],  
"boost": 1.0  
}  
}  
}

The "must\_not" condition used in the "hearingActionDetails.haaPartyRespRcvdOn" and "hearingActionDetails.haaPartyReplyRcvdOn" does not work as expected. it returns results where one of the field is not null

---

<div class="post-metadata">

**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\
**Post date:** [February 6, 2025, 9:43pm UTC](https://discuss.elastic.co/t/must-not-does-not-work-in-the-nested-array/374191/2 "2025-02-06T21:43:50Z")

</div>

Please share an example of the (to your understanding, incorrectly) matching document, the whole document, and the mapping please.

There's specific guidance in the docs for must\_not clauses and nested queries. I've no idea if it's helpful/applicable here.

> **[Nested query | Elasticsearch Guide \[8.17\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html)**

You posted a pretty similar Q recently !?

> [@The doucment is returned even if it does not meet the criteria](https://discuss.elastic.co/t/the-doucment-is-returned-even-if-it-does-not-meet-the-criteria/374145):
>
> I use the following DSL query "size": 25, "query": { "bool": { "must": [ { "term": { "hcAccountabilityOfficeCode.keyword": { "value": "550" } } }, { "nested": { "query": { "term": { "hearingActionDetails.haaActionTypeCode.keyword": { "value": "SU" } } }, "path": "hearingActionDetails", "ignore\_unmapped": false, "score\_mode": "none", "boost": 1.0 } }, { "nested": { "query": …

which is still open, waiting on your input.