# Filtering on missing field not working

**URL:** <https://discuss.elastic.co/t/filtering-on-missing-field-not-working/27716>\
**Category:** Elasticsearch\
**Created:** [August 19, 2015, 11:15pm UTC](https://discuss.elastic.co/t/filtering-on-missing-field-not-working/27716 "2015-08-19T23:15:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![richardking](https://avatars.discourse-cdn.com/v4/letter/r/ec9cab/32.png) [@richardking](https://discuss.elastic.co/u/richardking)\
**Post date:** [August 19, 2015, 11:15pm UTC](https://discuss.elastic.co/t/filtering-on-missing-field-not-working/27716/1 "2015-08-19T23:15:06Z")

</div>

In our document "Product" mapping, we have a field object called "codes"- example:

```
"codes" : [
{
"code" : "1234",
"place_id" : null
},
{
"code" : "4504",
"place_id" : 29
}
]

```

We are trying to write a query to retrieve all documents that have a codes.place\_id that is either null, or another number.

However, this query is not returning any results (only a portion of it below):

```
  "filter": {
    "and": [
      {
        "or": [
          {
            "missing": {
              "field": "codes.place_id",
              "existence": true,
              "null_value": true
            }
          },
          {
            "in": {
              "codes.place_id": [
                3
              ]
            }
          }
        ]
      }
    ]
  }

```

If I change the "in" clause to match the document's place\_id of 29, then it returns results properly. However, the original query should return results because that document has one code with a place\_id of null, right?

Here is the mapping:

```
{
  "product": {
    "properties": {
      "codes": {
        "properties": {
          "code": {
            "type": "string",
            "index": "not_analyzed",
            "fields": {
              "analyzed": {
                "type": "string"
              }
            }
          },
          "place_id": {
            "type": "long"
          }
        }
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![emptyemail](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@emptyemail](https://discuss.elastic.co/u/emptyemail)\
**Post date:** [August 20, 2015, 7:26pm UTC](https://discuss.elastic.co/t/filtering-on-missing-field-not-working/27716/2 "2015-08-20T19:26:52Z")

</div>

try removing

```auto
         "existence": true,
         "null_value": true

```

I think they are only required if you use null\_value mapping

---

<div class="post-metadata">

**Author:** ![richardking](https://avatars.discourse-cdn.com/v4/letter/r/ec9cab/32.png) [@richardking](https://discuss.elastic.co/u/richardking)\
**Post date:** [August 20, 2015, 7:51pm UTC](https://discuss.elastic.co/t/filtering-on-missing-field-not-working/27716/3 "2015-08-20T19:51:13Z")

</div>

I've tried every valid combination of existence/null\_value ☹

---

<div class="post-metadata">

**Author:** ![emptyemail](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@emptyemail](https://discuss.elastic.co/u/emptyemail)\
**Post date:** [August 20, 2015, 8:33pm UTC](https://discuss.elastic.co/t/filtering-on-missing-field-not-working/27716/4 "2015-08-20T20:33:43Z")

</div>

Another approach would be to use null\_value mapping in the mapping document, and then you can remove the entire OR clause and just use an IN clause and include the mapped null value in there. so if you map null to "-99999" then you can just do

```auto
{in: {"codes.place_id": [3, -99999]}}

```

---

<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:** [July 5, 2017, 11:54pm UTC](https://discuss.elastic.co/t/filtering-on-missing-field-not-working/27716/5 "2017-07-05T23:54:49Z")

</div>