The doucment is returned even if it does not meet the criteria

Elastic Stack Elasticsearch
1

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": {
						"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": {
						"exists": {
							"field": "hearingActionDetails.haaReceivedOn",
							"boost": 1.0
						}
					},
					"path": "hearingActionDetails",
					"ignore_unmapped": false,
					"score_mode": "none",
					"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 following document is returned even though it does not meet the criteria specified in the query.
The query clearly defined for "must_not" for "hearingActionDetails.haaPartyRespRcvdOn" and "hearingActionDetails.haaPartyReplyRcvdOn"

{
	"hcHearingCaseId": 274,
	"hcCaseNumber": "550-2025-00005",
	"hcStatusCode": "STS11",
	"hcAccountabilityOfficeCode": "550",
	"hcAccountabilityOfficeDescription": "San Francisco District Office",
	"hearingActionDetails": [
		{
			"haaHearingActionId": 374,
			"haaActionCatId": 954,
			"haaActionCatCode": "A",
			"haaActionCatDesc": "Motions",
			"haaActionTypeId": 920,
			"haaActionTypeCode": "SU",
			"haaActionTypeDesc": "Summary Judgement",
			"haaActionStatusId": 971,
			"haaActionStatusCode": "C",
			"haaActionStatusDescription": "Granted in Full for Complainant",
			"haaReqPartyTypeId": 927,
			"haaReqPartyTypeCode": "C",
			"haaReqPartyTypeDesc": "Complainant",
			"haaSurReplyStatusId": null,
			"haaSurReplyStatusCode": null,
			"haaSurReplyStatusDescription": null,
			"haaSurReplySubById": null,
			"haaSurReplySubByCode": null,
			"haaSurReplyReqByDescription": null,
			"haaReceivedOn": "01-03-2025",
			"haaPartyRespDueSetOn": null,
			"haaPartyRespDueOn": "01-21-2025",
			"haaPartyRespRcvdOn": "01-03-2025",
			"haaPartyReplyDueOn": "01-08-2025",
			"haaPartyReplyRcvdOn": "01-03-2025",
			"haaRespRcvdFrmAgcyOn": null,
			"haaRespRcvdFrmCompOn": null,
			"haaSurReplySubOn": null,
			"haaSurReplyRespDueOn": null,
			"haaSurReplyRespRcvdOn": null,
			"haaOrderDueOn": null,
			"haaIssuedOn": null,
			"haaRespRecdFrmAgcyOn": null,
			"haaRespRecdFrmCompOn": null,
			"hacCreatedOn": "01-03-2025",
			"hacMotionSurReplyRecdOn": null,
			"hacSurReplyStatusUpdatedOn": null
		}
	],
	"hcCaseAssigned": "N"
}
2

Hi @vinitp

With named query you can find out which clause hás match.

3

@RabBit_BR I have tested this with a nested mapping and I have received back no hits. :thinking:

Can you please share your index mapping, and the result for the explain API on your retrieved doc and query?

4

I do not see why this document would be returned. Have you by any chance altered it so we are not seeing the full document, e.g. by trimming the hearingActionDetails list to only contain the nested document you are focused on?

As you have several separate nested clauses each of these can be fulfilled by different objects in the hearningActionDetails array, causing the document to be returned. If you are looking for a nested document that fulfills all these nested criteria I believe you need to have a single nested clause with a bool query underneath holding the applicable criteria.

5

See similar thread, where the actual query in that thread is generated by a call to /sql/translate - @vinitp I would be curious if same is true here?

Opening multiple threads on effectively the same topic is, er, a little irritating.