# Not in other index

**URL:** <https://discuss.elastic.co/t/not-in-other-index/297588>\
**Category:** Elasticsearch\
**Created:** [February 18, 2022, 10:14am UTC](https://discuss.elastic.co/t/not-in-other-index/297588 "2022-02-18T10:14:45Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Loic\_Bajard](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loic_bajard/32/101981_2.png) [@Loic\_Bajard](https://discuss.elastic.co/u/Loic_Bajard)\
**Post date:** [February 18, 2022, 10:14am UTC](https://discuss.elastic.co/t/not-in-other-index/297588/1 "2022-02-18T10:14:45Z")

</div>

Considering these two indices:

```auto
## Mappings
PUT breeds
{
  "mappings": {
    "properties": {
      "size": { "type": "keyword" },
      "guarding_skills": { "type": "keyword" }
    }
  }
}

PUT dogs
{
  "mappings": {
    "properties": {
      "breed": { "type": "keyword" },
      "name": { "type": "keyword" },
    }
  }
}

## Datas
PUT breeds/_doc/german_sheperd
{
  "size": "large",
  "guarding_skills": "high"
}

PUT breeds/_doc/poodle
{
  "size": "small",
  "guarding_skills": "low"
}

PUT dogs/_doc/1
{
  "breed": "german_sheperd",
  "name": "cooper",
}

PUT dogs/_doc/2
{
  "breed": "poodle",
  "name": "duke",
}

PUT dogs/_doc/3
{
  "breed": "beagle",
  "name": "toby",
}

```

I make an aggregation on `/dogs` index to get unique list of breeds like this:

```auto
GET /dogs/_search
{
"size": 0,
"aggs": {
    "urls": {
      "terms": {
        "field": "breed"
      }
    }
  }
}

```

But I also would like to filter out breeds witch aren't in the `breeds` index, in this example, I would only get `beagle`.

I can make a query first to get a list of breeds from the `breeds` index and then use a must\_not with terms like this:

```auto
GET /dogs/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must_not": [
        {
          "terms": {
            "breed": [
              "german_sheperd",
              "poodle"
            ]
          }
        }
      ]
    },
    "aggs": {
      "urls": {
        "terms": {
          "field": "breed"
        }
      }
    }
  }
}

```

But I wish there is a more elegant way in a single query. Besides, my case involves a large amount of values (~10k) to use in the must\_not clause.

I'm pretty sure this kind of question has been asked many times, but I didn't find any similar case.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [February 18, 2022, 10:53am UTC](https://discuss.elastic.co/t/not-in-other-index/297588/2 "2022-02-18T10:53:49Z")

</div>

Bonjour Loic and welcome 🤗 .

What you want to do would mean doing "joins" in Elasticsearch, which is not really supported. From this page: [Joining queries | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html)

> Performing full SQL-style joins in a distributed system like Elasticsearch is prohibitively expensive. Instead, Elasticsearch offers two forms of join which are designed to scale horizontally.

You could look at [Join field type | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html)

But, the best option is to do the join at index time if doable.

---

<div class="post-metadata">

**Author:** ![Loic\_Bajard](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loic_bajard/32/101981_2.png) [@Loic\_Bajard](https://discuss.elastic.co/u/Loic_Bajard)\
**Post date:** [February 18, 2022, 11:22am UTC](https://discuss.elastic.co/t/not-in-other-index/297588/3 "2022-02-18T11:22:33Z")

</div>

Thanks for this answer.  
What do you mean by ?

> [@dadoonet](#):
>
> join at index time

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [February 18, 2022, 12:39pm UTC](https://discuss.elastic.co/t/not-in-other-index/297588/4 "2022-02-18T12:39:48Z")

</div>

Something like:

```auto
PUT dogs/_doc/1
{
  "breed": { 
    "type": "german_sheperd",
    "size": "large",
    "guarding_skills": "high"
  },
  "name": "cooper"
}

```

---

<div class="post-metadata">

**Author:** ![Loic\_Bajard](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loic_bajard/32/101981_2.png) [@Loic\_Bajard](https://discuss.elastic.co/u/Loic_Bajard)\
**Post date:** [February 18, 2022, 1:15pm UTC](https://discuss.elastic.co/t/not-in-other-index/297588/5 "2022-02-18T13:15:29Z")

</div>

Ok, I see, thanks.

---

<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:** [March 18, 2022, 1:16pm UTC](https://discuss.elastic.co/t/not-in-other-index/297588/6 "2022-03-18T13:16:15Z")

</div>

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