Elasticsearch nested query with multiple and and or conditions

Question

I am trying to generate a nested must and should query in elasticsearch to mimic the following condition:

(  (brandsvisited ilike '%adidas%' or   brandsvisited ilike '%skechers%' ) or( placecategory ilike '%Pizza Restaurant Visitors%' or   placecategory ilike '%Grocery Store Visitors%'  ) and( gender='Male'  )  ) and polygonid = '1465'

I created the following query in elasticsearch.

"query": {
      "bool": {
          "must": [
              {"term": {"polygonid": "1465"}},
              {"term": {"gender": "Male"}},
              {
                  "bool": {
                      "should": [
                          {"term": {"brandsvisited": "adidas"}},
                          {"term": {"brandsvisited": "skechers"}}
                      ],"minimum_should_match": 1
                  }
              },
              {
                  "bool": {
                      "should": [
                          {"term": {"placecategory": "Pizza Restaurant Visitors"}},
                          {"term": {"placecategory": "Grocery Store Visitors"}}
                      ],"minimum_should_match": 1
                  }
              }
          ]
      }
  }

The above query returns zero results. However, if I remove the last boolean should query, it returns the results. Not sure where I am going wrong.

Answer 1

Assuming that the placecategory field is of text type. You need to replace that with placecategory.keyword field (if you have not defined any explicit index mapping)

Term query returns documents that contain an exact term in a provided field. If you have not defined any explicit index mapping, then you need to add .keyword to the field. This uses the keyword analyzer instead of the standard analyzer.

Modify your query as shown below

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "polygonid": "1465"
          }
        },
        {
          "term": {
            "gender": "Male"
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "brandsvisited": "adidas"
                }
              },
              {
                "term": {
                  "brandsvisited": "skechers"
                }
              }
            ],
            "minimum_should_match": 1
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "placecategory.keyword": "Pizza Restaurant Visitors"
                }
              },
              {
                "term": {
                  "placecategory.keyword": "Grocery Store Visitors"
                }
              }
            ],
            "minimum_should_match": 1
          }
        }
      ]
    }
  }
}

If you want to search for an exact term in the placecategory then you can either use term query with should clause or terms query, otherwise, if you want to match phrase in the placecategory field, then you can use match_phrase query