Elasticsearch query not giving expected result

Question

This is the doc I have in my index(there can be several too):

{
        "_index" : "meeting",
        "_type" : "meeting",
        "_id" : "27",
        "_score" : 1.0,
        "_source" : {
          "createTime" : "2020-07-20T14:49:05.803",
          "createTimeInMs" : 1595234945803,
          "createdBy" : "[email protected]",
          "editTime" : "2020-07-20T14:49:05.803",
          "editTimeInMs" : 1595234945803,
          "editedBy" : "[email protected]",
          "versionId" : 1,
          "id" : "27",
          "projectId" : "62",
          "projectName" : "sdfsdf",
          "meetingName" : "meeting1",
          "agenda" : "string",
          "startDateString" : "2020-07-20T00:45:19.604",
          "userId" : 3,
          "memberList" : [
            3,
            4
          ],
          "status" : "ACTIVE",
          "timeZone" : "Asia/Dhaka",
          "startDate" : "2020-07-20T00:45:19.604",
          "endDate" : "2020-07-20T01:45:19.604",
          "dateInMS" : 1595227519000,
          "meetingPlace" : "string",
          "endDateString" : "2020-07-20T01:45:19.604"
        }
      }

Logically, I am trying to build this condItion:

if((projectId==62 && startDate>=inputFrom && startDate <=inputTo) && (status==ACTIVE ||status==POSTPONED))

My Query(from kibana):

GET /meeting/_search?pretty
{
  "query": 
  {
    "bool" : {
    "must" : [
      {
        "term" : {
          "projectId" : {
            "value" : "62",
            "boost" : 1.0
          }
        }
      },
      {
        "terms" : {
          "status" : [
            "ACTIVE",
            "POSTPONED"
          ],
          "boost" : 1.0
        }
      },
      {
        "range" : {
          "startDate" : {
            "from" : "2020-07-19T23:45:19.604Z",
            "to" : "2020-07-20T00:49:19.604Z",
            "include_lower" : true,
            "include_upper" : true,
            "boost" : 1.0
          }
        }
      }
    ],
    "adjust_pure_negative" : true,
    "boost" : 1.0
  }
  }
}

I am comparing with range query for startDate field within mentioned range with other must fields above. But not getting any hit! I want to retrieve documents which has startDate within the range of given from and to date.
Being quite inexperienced in this arena, don't know why not working! Please help how to fix this query to do that?

Answer 1

You can use bool to combine multiple queries in this way. The must clause will work as logical AND, and will make sure all the conditions are matched.

To return documents that contain terms within a provided range refer this ES official documentation on Range Query

Since you have not provided any mapping for the data you have indexed, I have not specified any explicit mapping for the index.

The below search query will give you the expected result according to your condition specified in the question.

Search Query:

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "projectId": "62"
          }
        },
        {
          "range": {
            "startDate": {
              "gte": "2020-07-20T00:44:19.604",
              "lte": "2020-07-21T00:45:19.604"
            }
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "status.keyword": "ACTIVE"
                }
              },
              {
                "term": {
                  "status.keyword": "POSTPONED"
                }
              }
            ]
          }
        }
      ]
    }
  }
}