# Using missing and exists filters - null values edge case

**URL:** <https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600>\
**Category:** Elasticsearch\
**Created:** [November 1, 2016, 6:10pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600 "2016-11-01T18:10:42Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![gmajivu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gmajivu/32/12878_2.png) [@gmajivu](https://discuss.elastic.co/u/gmajivu)\
**Post date:** [November 1, 2016, 6:10pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600/1 "2016-11-01T18:10:42Z")

</div>

I have a use case involving null\_values - I need to show country in the results whether it is set or it is null upon filtering by country field. I have indexed user profiles with a _country_ field which may be null (not set) or have a value. I have tried to play around with boolean query with exists and missing filters.

filter\_1.json

```
{
    "query": {
        "bool": {
            "must": {
                "match": {
                    "full_name": "xxx xxx xxx"
                }
            },
            "filter": {
                "bool": {
                    "must": [{"term": { "gender": "M" }}],
                    "filter": [{"missing": {"field": "country"}}]
                }
            }
        }
    }
}

```

which gives:

```
$ curl "http://127.0.0.1:9200/profile/_search?pretty" --data-binary @filter_1.json | grep country
    "country" : null,
    "country" : null,
    "country" : null,
    "country" : null,
    "country" : null,

```

the converse:

filter\_2.json

```
{"existing": {"field": "country"}}

```

which gives:

```
$ curl "http://127.0.0.1:9200/profile/_search?pretty" --data-binary @filter_2.json | grep country
    "country" : "SO",
    "country" : "SO",
    "country" : "SO",
    "country" : "SO",
    "country" : "SO",
    "country" : "SO",

```

Lastly, I have tried to adjust the filter in boolean query thus:

filter\_3.json

```
"filter": {
    "or": [
        {"missing": {"field": "country"}},
        {"exists": {"field": "country"}}
    ]
}

```

Ideal output I want:

```
"country" : "SO",
"country" : "SO",
"country" : "SO",
"country" : null,
"country" : "SO",
"country" : null,

```

Any ideas on what to try?

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [November 1, 2016, 6:24pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600/2 "2016-11-01T18:24:42Z")

</div>

Why do you need the filter at all if you want both kind of documents, either with country set or the ones with missing values?

---

<div class="post-metadata">

**Author:** ![gmajivu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gmajivu/32/12878_2.png) [@gmajivu](https://discuss.elastic.co/u/gmajivu)\
**Post date:** [November 1, 2016, 6:26pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600/3 "2016-11-01T18:26:45Z")

</div>

country could have multiple values like KE, SO, CD etc. When a user opts to filter by country, then I do not want to leave out the profiles with no country set.

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [November 1, 2016, 6:38pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600/4 "2016-11-01T18:38:27Z")

</div>

What if you use something like:

```auto
"or": [
        {"missing": {"field": "country"}},
        {"match": {"country": "KE"}}
    ]

```

---

<div class="post-metadata">

**Author:** ![gmajivu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gmajivu/32/12878_2.png) [@gmajivu](https://discuss.elastic.co/u/gmajivu)\
**Post date:** [November 1, 2016, 6:46pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600/5 "2016-11-01T18:46:48Z")

</div>

Awesome! this works. Thanks @cbuescher. Should I be concerned about performance since the docs advocate for positive queries.

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [November 1, 2016, 6:48pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600/6 "2016-11-01T18:48:02Z")

</div>

I don't know what kind of system you need and what performance you expect, but generally speaking those queries are not the most expensive ones. So I wouldn't worry too much if it stays that simple.

---

<div class="post-metadata">

**Author:** ![gmajivu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gmajivu/32/12878_2.png) [@gmajivu](https://discuss.elastic.co/u/gmajivu)\
**Post date:** [November 1, 2016, 6:50pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600/7 "2016-11-01T18:50:32Z")

</div>

Ok, noted.

---

<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, 10:07pm UTC](https://discuss.elastic.co/t/using-missing-and-exists-filters-null-values-edge-case/64600/8 "2017-07-05T22:07:45Z")

</div>