CODEWITHSUNDEEP
elasticsearch
Muthukumar Marichamy
Muthukumar Marichamy

Reputation: 1214

Elastic search - handling the condition using must or must not query

We have a requirement if newId is there then we have to get the data less than todays date and if newId field is not there in the data then we have to get the data till expiry date + 2Months. I was trying below query but result has not come as expected.

 {
    "id":"234",
    "startDate":"23/07/2020",
    "endDate":"24/09/20202",
    "newId":"2345"
    },
   {
    "id":"234",
    "startDate":"23/07/2020",
    "endDate":"24/09/20202",
    "newId":null
    },
{
    "id":"235",
    "startDate":"23/07/2020",
    "endDate":"24/06/2020",
    "newId":"2345"
    },

Query that I was trying

{
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {}
        },
        {
          "bool": {
            "must": [
             
              {
                "bool": {
                  "must": [
                    {
                      "exists": {
                        "field": "newId"
                      }
                    },
                    {
                      "range": {
                        "endDate": {
                          "gte":"now/d"
                        }
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must_not": [
                       {
                      "exists": {
                        "field": "newId"
                      }
                    },
                   
                    {
                      "range": {
                        "endDate": {
                          "gte": "now-2M"
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Expected result

{ "id":"234", "startDate":"23/07/2020", "endDate":"24/09/20202", "newId":"2345" }, { "id":"234", "startDate":"23/07/2020", "endDate":"24/09/20202", "newId":null },

Upvotes: 0

Views: 127

Answers (1)

Val
Val

Reputation: 217254

Great start! Your query is almost right, but you need a few more tweaks, namely to use should instead of must, because both sub-queries will never be true at the same time:

{
  "query": {
    "bool": {
      "minimum_should_match": 1,
      "should": [
        {
          "bool": {
            "must": [
              {
                "exists": {
                  "field": "newId"
                }
              },
              {
                "range": {
                  "endDate": {
                    "gte": "now/d"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "range": {
                  "endDate": {
                    "gte": "now-2M"
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "newId"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Upvotes: 1

Related Questions