How to query a nested array for an object with one matching field and one nonexistent field?

[A111 (A)]

Given this:

{
    "mappings": {
        "document_type": {
            "properties": {
                "id": {
                    "type": "long"
                },
                "resolutions": {
                    "type": "nested",
                    "properties": {
                        "employeeId": {
                            "type": "long"
                        },
                        "divisionId": {
                            "type": "long"
                        }
                    }
                }
            }
        }
    }
}

Is it possible to filter only those documents, that have at least one resolution with divisionId equal with something AND employeeId either null or nonexistent?

I haven't found a way, and queries such as the one below will check all objects in the array, and if at least on of them has employeeId then the whole document will not be returned, even if the array actually contains a result with the matching divisionId and no employeeId.

{
    "query": {
        "bool": {
            "must": [
                {
                    "nested": {
                        "path": "resolutions",
                        "query": {
                            "terms": {
                                "resolutions.divisionId": [
                                    660
                                ]
                            }
                        }
                    }
                }
            ],
            "must_not": [
                {
                    "nested": {
                        "path": "resolutions",
                        "query": {
                            "exists": {
                                "field": "resolutions.employeeId"
                            }
                        }
                    }
                }
            ]
        }
    }
}

[aadhikari3711]

Hi, try below script in elastic console . I had success using it with my index.

## It should be able to get result you want
GET /_sql?format=csv
{
  "query": """SELECT resolutions.divisionId
    FROM "indexname" 
    WHERE  resolutions.divisionId=660
         and resolutions.employeeId is null
    """

}

## It will convert sql to json -DSL
GET /_sql/translate&pretty
{"query": """SELECT resolutions.divisionId
    FROM "indexname" 
    WHERE  resolutions.divisionId=660
        and  resolutions.employeeId is null
    """
  
}