# Query with exclusion list

**URL:** <https://discuss.elastic.co/t/query-with-exclusion-list/232717>\
**Category:** Elasticsearch\
**Created:** [May 14, 2020, 9:33pm UTC](https://discuss.elastic.co/t/query-with-exclusion-list/232717 "2020-05-14T21:33:04Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![malinal](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@malinal](https://discuss.elastic.co/u/malinal)\
**Post date:** [May 14, 2020, 9:33pm UTC](https://discuss.elastic.co/t/query-with-exclusion-list/232717/1 "2020-05-14T21:33:05Z")

</div>

I’m still a beginner and I’ve run into a task I don’t know how to solve.

We have a small system that has one index only. We store simple objects with many properties Most data types are simple keyword (or date) because these are the best for our searches. The number of documents is ~ 1 million.

The query is e.g. "I am looking for all women aged 25-50 who live in Budapest". (our queries are much more complex than this)

```auto
GET /contact/_search
{
  "query": {
      "bool": {
        "should": [
          {
              "term": {
                  "city": "Budapest"
              }
          },
          {
              "term": {
                  "gender": "female"
              }
          },
          {
              "range": {
                  "birthdate": {
                      "lte": "1995-05-14",
                      "gte": "1970-05-14"
                  }
              }
          }
        ]
      }
  }
}

```

This works very well for us, extremely fast.  
Now, we want to query e.g. "I am looking for all women aged 20-50 who live in Budapest and do not have their ID on an exclusion list."

The exclusion list is currently e.g. a csv file with a hash column and e.g. 250,000 or 3,000,000 records.

Unfortunately, the exclusion list is never the same. This exclusion list is similar to NOT IN, NOT EXISTS in SQL. In one table the contacts, in another table the exclusion list: SELECT c. \* FROM contact c WHERE c.age BETWEEN 20-50 AND c.city = 'Budapest' and NOT EXISTS (SELECT 1 FROM exlusion e WHERE c.md5hash = e.md5hash).

How could I achieve this result? What is the best method? Should I put the exclusion list in another index and then query the two indexes combined?

Thanks,  
Laszlo

---

<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:** [June 11, 2020, 9:33pm UTC](https://discuss.elastic.co/t/query-with-exclusion-list/232717/2 "2020-06-11T21:33:09Z")

</div>

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