# How do I create a query to perform the following

**URL:** <https://discuss.elastic.co/t/how-do-i-create-a-query-to-perform-the-following/109400>\
**Category:** Elasticsearch\
**Created:** [November 28, 2017, 1:33pm UTC](https://discuss.elastic.co/t/how-do-i-create-a-query-to-perform-the-following/109400 "2017-11-28T13:33:28Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![iitgrad](https://avatars.discourse-cdn.com/v4/letter/i/9de053/32.png) [@iitgrad](https://discuss.elastic.co/u/iitgrad)\
**Post date:** [November 28, 2017, 1:33pm UTC](https://discuss.elastic.co/t/how-do-i-create-a-query-to-perform-the-following/109400/1 "2017-11-28T13:33:29Z")

</div>

My docs contain a single key value pair where the value is an array of words. I would like to find all docs that contain at least one of say “foo”, “bar”, “cloud” but exclude any that might have these words but also other words not specified in the original list. For example just foo passes. foo and bar pass. But foo and “sunny” is excluded since sunny is not in the acceptable list

---

<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:** [November 28, 2017, 2:20pm UTC](https://discuss.elastic.co/t/how-do-i-create-a-query-to-perform-the-following/109400/2 "2017-11-28T14:20:51Z")

</div>

Hi Kevin,

It seems that you can use the operator AND in your query:

First index the document:

```
POST my_index/doc/1
{
  "my_array": ["foo", "bar", "cloud"]
}

```

Use operator AND from match query:

```
POST my_index/_search
{
  "query": {
    "match": {
      "my_array": {
        "query": "foo bar",
        "operator": "AND"
      }
    }
  }
}

{
  "took": 0,
  ...
  "hits": {
    ...
    "hits": [
      {
        "_index": "my_index",
        "_type": "doc",
        "_id": "1",
        "_score": 0.5753642,
        "_source": {
          "my_array": [
            "foo",
            "bar",
            "cloud"
          ]
        }
      }
    ]
  }
}

```

Terms not present in the documents:

```
POST my_index/_search
{
  "query": {
    "match": {
      "my_array": {
        "query": "foo sunny",
        "operator": "AND"
      }
    }
  }
}

{
  "took": 0,
  ...
  "hits": {
    "total": 0,
    "max_score": null,
    "hits": []
  }
}

```

Hope it helps!  
LG

---

<div class="post-metadata">

**Author:** ![iitgrad](https://avatars.discourse-cdn.com/v4/letter/i/9de053/32.png) [@iitgrad](https://discuss.elastic.co/u/iitgrad)\
**Post date:** [November 28, 2017, 2:32pm UTC](https://discuss.elastic.co/t/how-do-i-create-a-query-to-perform-the-following/109400/3 "2017-11-28T14:32:48Z")

</div>

Thanks luiz. So my situation is that it can ONLY have one or more of the set and no more. So for example, if I have 3 docs which are:  
{  
name: "doc1",  
my\_array: ["a", "b", "c", "d"]  
}  
{  
name: "doc2",  
my\_array: ["a","b"]  
}  
{  
name: "doc3",  
my\_array: ["a"]  
}  
My criteria is must have EITHER "a" or "b" or both but no more  
So, doc1 fails, doc 2 and 3 pass

---

<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:** [November 29, 2017, 7:19pm UTC](https://discuss.elastic.co/t/how-do-i-create-a-query-to-perform-the-following/109400/4 "2017-11-29T19:19:26Z")

</div>

Hi Kevin,

That can be a little trick, but inspired on [Finding Multiple Exact Values](https://www.elastic.co/guide/en/elasticsearch/guide/current/_finding_multiple_exact_values.html) article you could do this:

```
POST my_index/doc/1
{
  "my_array": ["a","b","c","d"],
  "array_count": 4
}

POST my_index/doc/2
{
  "my_array": ["a","b"],
  "array_count": 2
}

POST my_index/doc/3
{
  "my_array": ["a"],
  "array_count": 1
}

```

Build your query combining multiple terms:

```
POST my_index/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "match": {
                  "my_array": {
                    "query": "a"
                  }
                }
              },
              {
                "match": {
                  "my_array": {
                    "query": "b"
                  }
                }
              },
              {
                "match": {
                  "array_count": 2
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "match": {
                  "my_array": {
                    "query": "a"
                  }
                }
              },
              {
                "match": {
                  "array_count": 1
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "match": {
                  "my_array": {
                    "query": "b"
                  }
                }
              },
              {
                "match": {
                  "array_count": 1
                }
              }
            ]
          }
        }
      ]
    }
  }
}

```

Please let me know if that works for you.

Cheers,  
LG

---

<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:** [December 27, 2017, 7:19pm UTC](https://discuss.elastic.co/t/how-do-i-create-a-query-to-perform-the-following/109400/5 "2017-12-27T19:19:53Z")

</div>

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