Query: Get employee name "Mahesh" whose id is "200" and joining datetime is in a given date range and his epf status must be either 'NOK' or 'WRN'. (Possible values of epf_status are {OK,NOK,WRN,CANCELLED}.

I have written the following query, that matches epf_status also with OK, CANCELLED, but it must only match when epf_status is either 'NOK' or 'WRN'. What else do I need to change to make it work, as required?

GET myindex01/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "empname": { "query": "Mahesh", "operator": "AND" }
          }
        },
        {
          "match": {
            "empid": { "query": "200", "operator": "AND" }
          }
        },
        {
          "range": {
            "joining_datetime": {
              "gte": "2020-01-01T00:00:00",
              "lte": "2022-06-24T23:59:59"
            }
          }
        }
      ],
      "should": [
        { "match": { "epf_status": "NOK" } },
        { "match": { "epf_status": "WRN" } }
      ]
    }
  }
}

SAMPLE DATA:

{"Mahesh","200","2022-04-01","OK"}
{"Mahesh","200","2022-04-01","NOK"}
{"Mahesh","200","2022-04-01","WRN"}
{"Mahesh","200","2022-04-01","CANCELLED"}

REQUIRED OUTPUT:

{"Mahesh","200","2022-04-01","NOK"}
{"Mahesh","200","2022-04-01","WRN"}

CodePudding user response：

Tldr;

You could be using the terms query for that I believe.

Returns documents that contain one or more exact terms in a provided field.

To solve

GET myindex01/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "empname": { "query": "Mahesh", "operator": "AND" }
          }
        },
        {
          "match": {
            "empid": { "query": "200", "operator": "AND" }
          }
        },
        {
          "range": {
            "joining_datetime": {
              "gte": "2020-01-01T00:00:00",
              "lte": "2022-06-24T23:59:59"
            }
          }
        }
      ],
      "should": [
        { "terms": { "epf_status": ["NOK", "WRN"] } }
      ]
    }
  }
}