# Find documents which don't have some field in the doc

**URL:** <https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106>\
**Category:** Elasticsearch\
**Created:** [August 13, 2019, 9:30pm UTC](https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106 "2019-08-13T21:30:48Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![ab\_cd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ab_cd/32/46709_2.png) [@ab\_cd](https://discuss.elastic.co/u/ab_cd)\
**Post date:** [August 13, 2019, 9:30pm UTC](https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106/1 "2019-08-13T21:30:48Z")

</div>

I'm trying to differentiate between documents that have some field but the value is not set, and the documents that don't have value. I was thinking about term query and script fields but both don't seem to let me achieve my goal. Could you advice?

My queries:

# See in the output if title is missing:

```auto
GET /employess_with_missing/_search
{
  "_source": {
    "includes": ["*"]
  },
  "script_fields": {
    "is_title_missing": {
      "script": {
        "lang": "painless", 
        "source" : "params._source.containsKey('title')"
      }
    }
  }
}

```

sorta works

```auto
"hits" : [
      {
        "_index" : "employess_with_missing",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "name" : "Bob Smith"
        },
        "fields" : {
          "is_title_missing" : [
            false
          ]
        }
      },
      {
        "_index" : "employess_with_missing",
        "_type" : "_doc",
        "_id" : "8",
        "_score" : 1.0,
        "_source" : {
          "name" : "John Smith",
          "title" : null,
          "age" : null
        },
        "fields" : {
          "is_title_missing" : [
            true
          ]
        }
      },
      {
        "_index" : "employess_with_missing",
        "_type" : "_doc",
        "_id" : "4",
        "_score" : 1.0,
        "_source" : {
          "name" : "Susan Smith",
          "title" : "Dev Mgr",
          "age" : 33
        },
        "fields" : {
          "is_title_missing" : [
            true
          ]
        }
      },

```

but if I try to do same check in the query

```auto
GET /employess_with_missing/_search
{
  "_source": {
    "includes": ["*"]
  },
  "script_fields": {
    "is_title_missing": {
      "script": {
        "lang": "painless", 
        "source" : "params._source.containsKey('title')"
      }
    }
  },
    "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "lang": "painless",
            "source": "params._source.containsKey('title')"
          }
        }
      }
    }
  }
}

```

I get null pointer exception

```auto
            "params._source.containsKey('title')",
            " ^---- HERE"
          ],
          "script": "params._source.containsKey('title')",
          "lang": "painless",
          "caused_by": {
            "type": "null_pointer_exception",
            "reason": null

```

* * *

If I try to replace **params.\_source** with **doc** on the query filter, it seems to be just ignored

# My test data

```auto
PUT /employess_with_missing/_doc/3?pretty
{
"name": "Bob Smith"
}

PUT /employess_with_missing/_doc/8?pretty
{
"name": "John Smith", "title": null, "age": null
}

PUT /employess_with_missing/_doc/4?pretty
{
"name": "Susan Smith", "title": "Dev Mgr", "age": 33
}

PUT /employess_with_missing/_doc/6?pretty
{
"name": "Jane Smith", "title": "Software Eng 2", "age": 25
}

```

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 14, 2019, 7:38am UTC](https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106/2 "2019-08-14T07:38:39Z")

</div>

try to use the [exists query](https://www.elastic.co/guide/en/elasticsearch/reference/7.3/query-dsl-exists-query.html) in combination with a `must_not` part of a [bool query](https://www.elastic.co/guide/en/elasticsearch/reference/7.3/query-dsl-bool-query.html)

---

<div class="post-metadata">

**Author:** ![ab\_cd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ab_cd/32/46709_2.png) [@ab\_cd](https://discuss.elastic.co/u/ab_cd)\
**Post date:** [August 14, 2019, 4:40pm UTC](https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106/3 "2019-08-14T16:40:10Z")

</div>

Thanks for response.

The query with must\_not exists doesn't do what I want to achieve, because it returns both documents:

1. where title is null
2. where title is not present in the doc

While I want to be able to find documents there title is not present in the doc **only**.

```auto
GET /employess_with_missing/_search
{
  "_source": {
    "includes": ["name", "title"]
    }
    , 
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "title"
        }
      }
    }
  }
}

    "hits" : [
      {
        "_index" : "employess_with_missing",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 0.0,
        "_source" : {
          "name" : "Bob Smith"
        }
      },
      {
        "_index" : "employess_with_missing",
        "_type" : "_doc",
        "_id" : "8",
        "_score" : 0.0,
        "_source" : {
          "name" : "John Smith",
          "title" : null
        }
      }
    ]

```

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 15, 2019, 7:14am UTC](https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106/4 "2019-08-15T07:14:15Z")

</div>

by default `null` is treated the same way than a field not being existent, as nothing is stored in the inverted index. If you want to change this behaviour take a look at the [null\_value mapping parameter](https://www.elastic.co/guide/en/elasticsearch/reference/7.3/null-value.html)

--Alex

---

<div class="post-metadata">

**Author:** ![ab\_cd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ab_cd/32/46709_2.png) [@ab\_cd](https://discuss.elastic.co/u/ab_cd)\
**Post date:** [August 16, 2019, 6:35pm UTC](https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106/5 "2019-08-16T18:35:40Z")

</div>

Thanks Alexander.

So to clarify my understanding:

Because there's nothing stored in the inverted index, nothing of the existing (exists, script) can differentiate non-existing field from the field with null value in common case, for query/filter contexts?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 19, 2019, 6:45am UTC](https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106/6 "2019-08-19T06:45:44Z")

</div>

yes, that is correct.

---

<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:** [September 16, 2019, 6:56am UTC](https://discuss.elastic.co/t/find-documents-which-dont-have-some-field-in-the-doc/195106/7 "2019-09-16T06:56:48Z")

</div>

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