# Query ElasticSearch for documents with terms matching exclusively

**URL:** <https://discuss.elastic.co/t/query-elasticsearch-for-documents-with-terms-matching-exclusively/128424>\
**Category:** Elasticsearch\
**Created:** [April 17, 2018, 7:32pm UTC](https://discuss.elastic.co/t/query-elasticsearch-for-documents-with-terms-matching-exclusively/128424 "2018-04-17T19:32:43Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dierre](https://avatars.discourse-cdn.com/v4/letter/d/f6c823/32.png) [@dierre](https://discuss.elastic.co/u/dierre)\
**Post date:** [April 17, 2018, 7:32pm UTC](https://discuss.elastic.co/t/query-elasticsearch-for-documents-with-terms-matching-exclusively/128424/1 "2018-04-17T19:32:43Z")

</div>

I was porting a SQL query on ElasticSearch.

The query needs to substitute an `IN` clause.

Following this [link](https://stackoverflow.com/questions/30111258/elasticsearch-in-equivalent-operator-in-elasticsearch/30114975#30114975) I implemented the `IN` this way:

```
{
  "size": 1,
  "query": {
    "constant_score": {
      "filter": {
        "bool": {
          "must": [
            {
              "terms": {
                "products.flights.legs.hops.hopFlight.airlineId": [
                  "ib",
                  "lh"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

```

It is working but probably I have a special case: you see, the hops field in the document is an array, so for a flight with one stop we have two hops.

In that case this query works even when just one of the two hops has _ib_ or _lh_. It matches a document even when only one of the two hops has one of the airlines and the other hop is a different airline, not include in my terms.

I actually want to return only documents that, as airlineId, have only _ib_ or _lh_ or a _combination of both_.

Is it possible to do it?

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [April 17, 2018, 8:50pm UTC](https://discuss.elastic.co/t/query-elasticsearch-for-documents-with-terms-matching-exclusively/128424/2 "2018-04-17T20:50:46Z")

</div>

> [@dierre](#):
>
> Is it possible to do it?

I don't quite understand what exactly you are trying to achieve and without seeing your mapping and test data it's a bit difficult to show a concrete solution, but take a look at the [terms set](https://www.elastic.co/guide/en/elasticsearch/reference/6.2/query-dsl-terms-set-query.html) query. I might be wrong, but I think this is what you are looking for.

---

<div class="post-metadata">

**Author:** ![dierre](https://avatars.discourse-cdn.com/v4/letter/d/f6c823/32.png) [@dierre](https://discuss.elastic.co/u/dierre)\
**Post date:** [April 18, 2018, 7:17am UTC](https://discuss.elastic.co/t/query-elasticsearch-for-documents-with-terms-matching-exclusively/128424/3 "2018-04-18T07:17:49Z")

</div>

Hello Igor,  
I'll try to be more clear. Let's say I have these documents:

**Document A**

```
{
  "group" : "fans",
  "hops" : [ 
    {
      "airlineId" : "ih",
      "last" : "Smith"
    },
    {
      "airlineId" : "u2",
      "last" : "White"
    }
  ]
}

```

**Document B**

```
{
  "group" : "fans",
  "user" : [ 
    {
      "airlineId" : "lh",
      "last" : "Smith"
    },
    {
      "airlineId" : "lh",
      "last" : "White"
    }
  ]
}

```

**Document C**

```
{
  "group" : "fans",
  "user" : [ 
    {
      "airlineId" : "ib",
      "last" : "Smith"
    },
    {
      "airlineId" : "lh",
      "last" : "White"
    }
  ]
}

```

If I execute the query

```
{
  "size": 3,
  "query": {
    "constant_score": {
      "filter": {
        "bool": {
          "must": [
            {
              "terms": {
                "products.flights.legs.hops.hopFlight.airlineId": [
                  "ib",
                  "lh"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

```

the result will be: **Document A** , **Document B** and **Document C**.

What I actually want is just **Document B** and **Document C** because they include hops with just _ib_, _lh_ or _both of them_, the document with _u2_ it's not correct for my use case.

I think that the problem is in the index I'm using which is:

```
"airlineId": {
    "type": "string",
    "analyzer": "just_lowercase",
    "ignore_above": 10922
    }

```

which is simple string, but at the moment I cannot change it.

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [April 20, 2018, 5:42am UTC](https://discuss.elastic.co/t/query-elasticsearch-for-documents-with-terms-matching-exclusively/128424/4 "2018-04-20T05:42:56Z")

</div>

You will probably need to add an additional field with a de-duped list of airline ids from all hops and then use terms set query on it.

---

<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:** [May 18, 2018, 5:42am UTC](https://discuss.elastic.co/t/query-elasticsearch-for-documents-with-terms-matching-exclusively/128424/5 "2018-05-18T05:42:57Z")

</div>

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