# How to: Elastic Search - Efficient/Compact “Not In” Operator

**URL:** https://discuss.elastic.co/t/how-to-elastic-search-efficient-compact-not-in-operator/111975
**Category:** Elasticsearch
**Created:** [December 15, 2017, 11:58am UTC](https://discuss.elastic.co/t/how-to-elastic-search-efficient-compact-not-in-operator/111975 "2017-12-15T11:58:54Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![menelaosbgr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/menelaosbgr/32/25664_2.png) [@menelaosbgr](https://discuss.elastic.co/u/menelaosbgr)
#### Post date: [December 15, 2017, 11:58am UTC](https://discuss.elastic.co/t/how-to-elastic-search-efficient-compact-not-in-operator/111975/1 "2017-12-15T11:58:54Z")

</div>

So I wanted the equivalent of doing an sql not in query such as this:

select \* from test where id not in (1,2,3);  
Initially I was thinking more in SQL, and it took me an hour to understand the philosophy behind Elasticsearch, the query builder, and the underlying rest API.

I have come up with the following which however seems very inefficient:

```
public <T> MyQueryBuilder notIn(String field, List<String> values) {
        if (values != null & values.size() > 0) {

            Iterator<String> it = values.iterator();

            while(it.hasNext()){
                MatchQueryBuilder match = new MatchQueryBuilder(field, it.next());
                match.operator(Operator.OR);
                boolQueryBuilder.mustNot(match);
            }
        }
        return this;
    }

```

And this generates the following rest call body:

```
{
  "bool" : {
    "must_not" : [ {
      "match" : {
        "myId" : {
          "query" : "b5359d78-5e0e-4b09-af2c-d4c921ea6a15",
          "operator" : "OR"
        }
      }
    }, {
      "match" : {
        "myId" : {
          "query" : "e679ce6d-a7a1-48a0-b06e-54752fbd7e31",
          "operator" : "OR"
        }
      }
    } ]
  }
}

```

So is there a better more efficient way? Update: Efficient might have been the wrong word. A more concise/compact expression....

Thanks!

Update

I also tried the following but it does not work like an sql not in expression:

> { "query" : {  
> "bool" : {  
> "must\_not" : {  
> "bool": {  
> "filter": [  
> {  
> "terms": {  
> "myId": [  
> "c1928da6-80d0-475a-9025-b3ebd934a576" ,  
> "d2",  
> "d3",  
> "d4"  
> ]  
> }  
> }  
> ]  
> }  
> },  
> "must\_not" : {  
> "match" : {  
> "myId" :  
> }  
> }  
> }  
> }  
> }  
> Update 2

I think the following actually works, and works better:

```
{ "query" : {
        "bool" : {
            "must_not" : {
                      "bool": {
                            "filter": [
                              {
                                "terms": {
                                  "myId": [
                                     "c1928da6-80d0-475a-9025-b3ebd934a576" ,
                                    "d2",
                                    "d3",
                                    "d4"
                                  ]
                                }
                              }
                            ]
                          }
            }
        }
    }
}

```

This does not seem to work even though the notation seems to be more concise .

```
public <T> CoolQueryBuilder notInV2(String field, List<String> values) {
    if (values != null & values.size() > 0) {
        //boolQuery() = constructor for boolean query
        boolQueryBuilder.mustNot(boolQuery().filter(termsQuery(field, values)));
    }
    return this;
}

```

---

<div class="post-metadata">

### Author: ![luiz.santos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luiz.santos/32/24664_2.png) [@luiz.santos](https://discuss.elastic.co/u/luiz.santos)
#### Post date: [December 22, 2017, 3:10pm UTC](https://discuss.elastic.co/t/how-to-elastic-search-efficient-compact-not-in-operator/111975/2 "2017-12-22T15:10:00Z")

</div>

Hi @menelaosbgr,

You don't need that inner filter, you can use must\_not only. People coming from SQL also usually disregard the fact that fields are analyzed by default in elasticsearch. To filter ID probably you might be interested in use [keyword type in your field](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html).

The complete example:

```
//create index mapping myId of type keyword
PUT not_in_example
{
  "mappings": {
    "doc": {
      "properties": {
        "myId": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    }
  }
}

POST not_in_example/doc/1
{
  "myId": "d0"
}

POST not_in_example/doc/2
{
  "myId": "d2"
}

POST not_in_example/doc/3
{
  "myId": "c1928da6-80d0-475a-9025-b3ebd934a576"
}

```

Search/Result:

```
GET not_in_example/_search
{
  "query": {
    "bool": {
      "must_not": {
        "terms": {
          "myId": [
            "c1928da6-80d0-475a-9025-b3ebd934a576",
            "d2",
            "d3",
            "d4"
          ]
        }
      }
    }
  }
}

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "not_in_example",
        "_type": "doc",
        "_id": "1",
        "_score": 1,
        "_source": {
          "myId": "d0"
        }
      }
    ]
  }
}
```

---

<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: [January 19, 2018, 3:10pm UTC](https://discuss.elastic.co/t/how-to-elastic-search-efficient-compact-not-in-operator/111975/3 "2018-01-19T15:10:33Z")

</div>

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