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.

Why are you using terms query if you have to search for '%Pizza Restaurant Visitors%'? — Tushar Shahi, Jun 16 '21 at 05:11
@TusharShahi I could have used match also...I am completely new to elasticsearch and not sure which one is right. — Apricot, Jun 16 '21 at 05:29
Check this https://stackoverflow.com/questions/26001002/elasticsearch-difference-between-term-match-phrase-and-query-string. Check what ESCoder has mentioned about matchphrase. — Tushar Shahi, Jun 16 '21 at 05:31
@TusharShahi Thank you for that question...I tried with `match` and it started returning data — Apricot, Jun 16 '21 at 05:31

ESCoder · Accepted Answer · 2021-06-16T05:18:33.927

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

@Apricot `match` query will not give you the correct answer, as it would match all the documents that have either `Pizza` or `Restaurant` or `Visitors` in the `placecategory` field. However according to your SQL query `match_phrase` would give you the correct answer — ESCoder, Jun 16 '21 at 05:32
Many thanks, I tried all combinations for my use case terms with keyword, match and match_phrase....you suggestion on `match_phrase` works perfectly...Thank you for your continuous support and detailing. — Apricot, Jun 16 '21 at 06:07

Elasticsearch nested query with multiple and and or conditions

1 Answers1