# Find nested objects that are null

**URL:** <https://discuss.elastic.co/t/find-nested-objects-that-are-null/99578>\
**Category:** Elasticsearch\
**Created:** [September 6, 2017, 12:04pm UTC](https://discuss.elastic.co/t/find-nested-objects-that-are-null/99578 "2017-09-06T12:04:20Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sina309](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sina309/32/15020_2.png) [@sina309](https://discuss.elastic.co/u/sina309)\
**Post date:** [September 6, 2017, 12:04pm UTC](https://discuss.elastic.co/t/find-nested-objects-that-are-null/99578/1 "2017-09-06T12:04:20Z")

</div>

I have a nested field called 'category':

```
PUT my_index
{
  "mappings": {
    "my_type": {
      "properties": {
        "name": {
          "type": "keyword",
          "null_value": "NULL"
        },
        "categories": {
          "type": "nested", 
          "properties": {
            "handle": {
              "type": "keyword",
              "null_value": "NULL"
            },
            "name": {
              "type": "keyword",
              "null_value": "NULL",
              "fields": {
                "text": {
                  "type": "text",
                  "norms": false
                }
              }
            }
          }
        }
      }
    }
  }
}

```

Data:

```
PUT my_index/my_type/_bulk?refresh
{"index":{"_id":1}}
{"name":null,"categories":[{"handle":null,"name":"books"}]}
{"index":{"_id":2}}
{"name":[],"categories":[null]}
{"index":{"_id":3}}
{"name":"philip","categories":null}
{"index":{"_id":4}}
{"name":["philip"]}
{"index" : {"_id":5}}
{"name":[null]}
{"index": {"_id": 6}}
{"query":{"term":{"name":"NULL"}}}

```

Using the 5.X version, I want to get the NULL fields. Following works find:

```
GET my_index/_search
{
  "query": {
    "nested": {
      "path": "categories",
      "query": {
        "term": {
          "categories.handle": "NULL"
        }
      }
    }
  }
}

```

But how can I check if a whole nested object is null. The following query does not work:

```
GET my_index/_search
{
  "query": {
    "nested": {
      "path": "categories",
      "query": {
        "term": {
          "categories": "NULL"
        }
      }
    }
  }
}

```

How can I make a query that gives me ids **2** and **3** for null categories as result?

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [September 6, 2017, 4:31pm UTC](https://discuss.elastic.co/t/find-nested-objects-that-are-null/99578/2 "2017-09-06T16:31:19Z")

</div>

You can't: empty arrays, arrays that only contain nulls and non-existing arrays are the same to Elasticsearch. The closest that you can do is to find all documents that do not have non-null non-empty categories:

```auto
GET my_index/_search 
{
  "query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "exists": {
                "field": "categories"
              }
            }
          }
        }
      ]
    }
  }
}

```

---

<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:** [October 5, 2017, 9:11am UTC](https://discuss.elastic.co/t/find-nested-objects-that-are-null/99578/4 "2017-10-05T09:11:58Z")

</div>

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