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
– Tushar Shahi, Commented Jun 16, 2021 at 5:11
@TusharShahi I could have used match also...I am completely new to elasticsearch and not sure which one is right. — Apricot
– Apricot, Commented Jun 16, 2021 at 5:29
Check this stackoverflow.com/questions/26001002/…. Check what ESCoder has mentioned about matchphrase. — Tushar Shahi
– Tushar Shahi, Commented Jun 16, 2021 at 5:31
@TusharShahi Thank you for that question...I tried with match and it started returning data — Apricot
– Apricot, Commented Jun 16, 2021 at 5:31

Bhavya · Accepted Answer · 2021-06-16 05:18:33Z

0

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

edited Jun 16, 2021 at 5:18

answered Jun 16, 2021 at 5:08

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bhavya Over a year ago

@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

Apricot Over a year ago

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.

Bhavya Over a year ago

@Apricot glad I could help you :-)

Collectives™ on Stack Overflow

Elasticsearch nested query with multiple and and or conditions

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related