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" : "sdf@sdfsd.com",
          "editTime" : "2020-07-20T14:49:05.803",
          "editTimeInMs" : 1595234945803,
          "editedBy" : "sfsf@sdfsdf.com",
          "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?

score 1 · Accepted Answer · answered Jul 21 '20 at 08:31

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"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

thanks. Later I have followed this approach just without bool for should clause and serves my purposes! — user404, Jul 21 '20 at 08:55
@user404 okay :) Since u have not provided any answer of your own, (and this answer also solves your problem) so can u please accept this answer as it would help others also to resolve their issue if they got stuck in a similar type of issue :) And thank u for upvoting the answer :) — ESCoder, Jul 21 '20 at 09:01
I appreciate your efforts ! And accepting as mine and yours are almost similar — user404, Jul 21 '20 at 10:17

Elasticsearch query not giving expected result

1 Answers1