Search code examples
elasticsearchdate-rangeelasticsearch-dsl

Elastic Search combination of with Multiple Range, Term filters with And and Or operators


I have a filter with multiple data range filter with And and OR operators. I have to get filter results which satisfies both date range filters or any one of the date range filter.

 "query":{
    "bool" : {
    "must" : [
      {
        "match_phrase_prefix" : {
          "searchField" : {
            "query" : "Adam",
            "slop" : 0,
            "max_expansions" : 50,
            "boost" : 1.0
          }
        }
      }
    ],
    "filter" : [
      {
        "term" : {
          "srvcType" : {
            "value" : "FullTime",
            "boost" : 1.0
          }
        }
      },
      {"range" : { "or": {"startDt": {"from" : "2010-05-16","to" : "2022-02-18","include_lower": true,"include_upper" : true,"boost" : 1.0}} }},
      {"range" : { "or": {"endDt": {"from" : "2015-05-16","to" : "2022-02-18","include_lower" : true,"include_upper" : true,"boost" : 1.0}}}}
    ],
    "adjust_pure_negative" : true,
    "boost" : 1.0
  }
}
}

I tried to run the query like above, I got parsing_exception - query does not support StartDt.

{
 "query":{
    "bool" : {
    "must" : [
      {
        "match_phrase_prefix" : {
          "searchField" : {
            "query" : "Adam",
            "slop" : 0,
            "max_expansions" : 50,
            "boost" : 1.0
          }
        }
      }
    ],
    "filter" : [
      {
        "term" : {
          "srvcType" : {
            "value" : "FullTime",
            "boost" : 1.0
          }
        }
      },
      {"range" : {"startDt": {"from" : "2010-05-16","to" : "2022-02-18","include_lower": true,"include_upper" : true,"boost" : 1.0}} },
      {"range" : {"endDt": {"from" : "2015-05-16","to" : "2022-02-18","include_lower" : true,"include_upper" : true,"boost" : 1.0}}}
    ],
    "adjust_pure_negative" : true,
    "boost" : 1.0
  }
}
}

Solution

  • If you need AND semantics for your date range filters, you can let both range queries in the bool/filter array.

    However, if you need OR semantics you can use the bool/should query, like below:

    {
      "query": {
        "bool": {
          "must": [
            {
              "match_phrase_prefix": {
                "searchField": {
                  "query": "Adam",
                  "slop": 0,
                  "max_expansions": 50,
                  "boost": 1
                }
              }
            }
          ],
          "filter": [
            {
              "term": {
                "srvcType": {
                  "value": "FullTime",
                  "boost": 1
                }
              }
            }
          ],
          "minimum_should_match": 1,
          "should": [
            {
              "range": {
                "startDt": {
                  "from": "2010-05-16",
                  "to": "2022-02-18",
                  "include_lower": true,
                  "include_upper": true,
                  "boost": 1
                }
              }
            },
            {
              "range": {
                "endDt": {
                  "from": "2015-05-16",
                  "to": "2022-02-18",
                  "include_lower": true,
                  "include_upper": true,
                  "boost": 1
                }
              }
            }
          ],
          "adjust_pure_negative": true,
          "boost": 1
        }
      }
    }