Must_not does not work in the nested array

Elastic Stack Elasticsearch
1

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

2

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.

You posted a pretty similar Q recently !?

which is still open, waiting on your input.