# Need help with Elasticsearch query

**URL:** <https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399>\
**Category:** Elasticsearch\
**Created:** [February 7, 2020, 8:14pm UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399 "2020-02-07T20:14:35Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![ankitwandx](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ankitwandx/32/48587_2.png) [@ankitwandx](https://discuss.elastic.co/u/ankitwandx)\
**Post date:** [February 7, 2020, 8:14pm UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/1 "2020-02-07T20:14:36Z")

</div>

I am fairly new with ES queries. Need urgent help with this:

> <https://stackoverflow.com/questions/59949450/elasticsearch-query-having-multiple-bool-conditions>

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [February 7, 2020, 8:41pm UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/2 "2020-02-07T20:41:47Z")

</div>

Elastic has sql extension.  
I don't know much about rest query but this is how I will write sql

```
GET _sql?format=txt
{
  "query": """
              select uuid from "index-name" where keywords in ('google', 'microsoft','tesla') and 
              timestamp between 'date1' and 'date2' group by uuid
        """
}
```

---

<div class="post-metadata">

**Author:** ![ankitwandx](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ankitwandx/32/48587_2.png) [@ankitwandx](https://discuss.elastic.co/u/ankitwandx)\
**Post date:** [February 8, 2020, 6:39am UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/3 "2020-02-08T06:39:16Z")

</div>

Hey Sachin, thanks for your response.

But what I need is to get the uuids which satisfies both the conditions. That is,

`select uuid from "index-name" where (keywords in ('google', 'microsoft','tesla') and timestamp between 'date1' and 'date2') AND (keywords in ('apple', 'youtube'','spotify') and timestamp between 'date1' and 'date2') group by uuid`

In my example, it is user\_uuid 1234. How do I write this in ES query? Can this be done in a single query? do I need to use any aggregation? I am very new to ES query. Need help!

---

<div class="post-metadata">

**Author:** ![ankitwandx](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ankitwandx/32/48587_2.png) [@ankitwandx](https://discuss.elastic.co/u/ankitwandx)\
**Post date:** [February 10, 2020, 6:22am UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/4 "2020-02-10T06:22:36Z")

</div>

```
This works.
    {
  "query": {
    "bool": {
      "must": [
        {
        "bool": {
          "should": [
            { "term" : { "keywords" : "google" } },
            { "term" : { "keywords" : "microsoft" } },
            { "term" : { "keywords" : "tesla" } }
          ],
          "minimum_should_match": 1, 
          "must": [
            { "range": { 
                "@timestamp": {
                  "gte": "now-5d/d",
                  "lte": "now",
                  "format": "date_optional_time"
                } 
              } 
            }
          ]
        }}
      ]
    }
  },
  "aggs": {
         "uniq_uuids": {
             "terms": {
                 "field": "user_uuid.keyword",
                 "size": 10000
             }
         }
     }
}

```

But when I try to add another bool inside must array, it returns zero hits. See below:

```
POST _search
{
  "query": {
    "bool": {
      "must": [
        {
        "bool": {
          "should": [
            { "term" : { "keywords" : "google" } },
            { "term" : { "keywords" : "microsoft" } },
            { "term" : { "keywords" : "tesla" } }
          ],
          "minimum_should_match": 1, 
          "must": [
            { "range": { 
                "@timestamp": {
                  "gte": "now-5d/d",
                  "lte": "now",
                  "format": "date_optional_time"
                } 
              } 
            }
          ]
        }},
        {"bool": {
          "should": [
            { "term" : { "keywords" : "apple" } },
            { "term" : { "keywords" : "youtube" } },
            { "term" : { "keywords" : "spotify" } }
          ],
          "minimum_should_match": 1, 
          "must": [
            { "range": { 
                "@timestamp": {
                  "gte": "now-9d/d",
                  "lte": "now",
                  "format": "date_optional_time"
                } 
              } 
            }
          ]
        }}
      ]
    }
  },
  "aggs": {
         "uniq_uuids": {
             "terms": {
                 "field": "user_uuid.keyword",
                 "size": 10000
             }
         }
     }
}
```

---

<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 10, 2020, 10:01am UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/5 "2020-02-10T10:01:04Z")

</div>

Do you have a document which should match the second query? What does this document look like?

---

<div class="post-metadata">

**Author:** ![ankitwandx](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ankitwandx/32/48587_2.png) [@ankitwandx](https://discuss.elastic.co/u/ankitwandx)\
**Post date:** [February 10, 2020, 10:21am UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/6 "2020-02-10T10:21:53Z")

</div>

Yes. When I try the queries individually, they return correct documents. But when I add both the queries together inside must, it returns empty result.

For your ref:

> **[query](https://docs.google.com/document/d/1kE4uwA9hP92EOTuImVl4WTOdhcaS93lwq2Auy03pwr8/edit)**
>
> This works: { "query": { "bool": { "must": \[{ "bool": { "should": \[{ "term" : { "keywords" : "google" } },{ "term" : { "keywords" : "microsoft" } },{ "term" : { "keywords" : "tesla" } }\], ...

---

<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 10, 2020, 3:14pm UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/7 "2020-02-10T15:14:03Z")

</div>

You did not share a JSON document which is supposed to match your query.  
Could you do that please?

Even better: could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

---

<div class="post-metadata">

**Author:** ![ankitwandx](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ankitwandx/32/48587_2.png) [@ankitwandx](https://discuss.elastic.co/u/ankitwandx)\
**Post date:** [February 11, 2020, 9:11am UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/8 "2020-02-11T09:11:54Z")

</div>

Here's an example of the raw JSON of the documents:

```
{
  "user_uuid": 1234,
  "keywords": "apple",
  "@timestamp": "2020-01-15",
},
{
  "user_uuid": 1234,
  "keywords": "google",
  "@timestamp": "2020-01-21",
},
{
  "user_uuid": 9876,
  "keywords": "youtube",
  "@timestamp": "2020-01-25",
}

```

The query I am trying is mentioned above. Simple example query can be:

```
POST _search
{
  "query": {
    "bool": {
      "must": [
        {
        "bool": {
          "should": [
            { "term" : { "keywords" : "google" } },
            { "term" : { "keywords" : "microsoft" } },
            { "term" : { "keywords" : "tesla" } }
          ],
          "minimum_should_match": 1
        }},
        {"bool": {
          "should": [
            { "term" : { "keywords" : "apple" } },
            { "term" : { "keywords" : "youtube" } },
            { "term" : { "keywords" : "spotify" } }
          ],
          "minimum_should_match": 1
        }}
      ]
    }
  },
  "aggs": {
         "uniq_uuids": {
             "terms": {
                 "field": "user_uuid.keyword",
                 "size": 10000
             }
         }
     }
}

```

what I am trying to find is the user\_uuid which satisfies both the bool conditions. In this case, the user\_uuid **1234**. please let me know if I have not described the problem correctly.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [February 11, 2020, 9:41am UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/9 "2020-02-11T09:41:58Z")

</div>

Queries are designed to match properties of documents and user 1234 is not a single document.

Elasticsearch, like any other distributed system will have problems with your sorts of query if each user's documents can be spread across different machines. To tackle this you need to bring the related content closer together by either:

1. Storing each user's content in a single JSON document e.g. `{"user_uuid":1234, "keywords":["apple", "google"]}`
2. Storing each user's content in a single JSON document but indexing as multiple Lucene documents (see [nested](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) type).
3. Storing each user's content in separate JSON documents but hosted on the same machine (see [parent/child](https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html) )

---

<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 10, 2020, 9:42am UTC](https://discuss.elastic.co/t/need-help-with-elasticsearch-query/218399/10 "2020-03-10T09:42:03Z")

</div>

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