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

**URL:** <https://discuss.elastic.co/t/how-to-query-a-nested-array-for-an-object-with-one-matching-field-and-one-nonexistent-field/316663>\
**Category:** Elasticsearch\
**Created:** [October 14, 2022, 5:10pm UTC](https://discuss.elastic.co/t/how-to-query-a-nested-array-for-an-object-with-one-matching-field-and-one-nonexistent-field/316663 "2022-10-14T17:10:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![A111](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/a111/32/112088_2.png) [@A111](https://discuss.elastic.co/u/A111)\
**Post date:** [October 14, 2022, 5:10pm UTC](https://discuss.elastic.co/t/how-to-query-a-nested-array-for-an-object-with-one-matching-field-and-one-nonexistent-field/316663/1 "2022-10-14T17:10:13Z")

</div>

Given this:

```auto
{
    "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.

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

```

---

<div class="post-metadata">

**Author:** ![aadhikari3711](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aadhikari3711/32/111988_2.png) [@aadhikari3711](https://discuss.elastic.co/u/aadhikari3711)\
**Post date:** [October 14, 2022, 5:36pm UTC](https://discuss.elastic.co/t/how-to-query-a-nested-array-for-an-object-with-one-matching-field-and-one-nonexistent-field/316663/2 "2022-10-14T17:36:25Z")

</div>

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

```auto
## 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
    """
  
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 11, 2022, 5:36pm UTC](https://discuss.elastic.co/t/how-to-query-a-nested-array-for-an-object-with-one-matching-field-and-one-nonexistent-field/316663/3 "2022-11-11T17:36:51Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.