elasticsearch how to query with two different fields with data range

Question

I am using using elasticsearch, i have following fields in doc.

sale_start

sale_end

sale_price

selling_price

I want to query data which should be like:

if "sale_start" date and "sale_end" date is between the current date

then it should match the price range condition with sale_price

if "sale_start" date and "sale_end" date is not between the current date

then it should match the price range condition with selling_price

I searched a lot, but could not find a way to write condition like this. I am also new to elasticsearch. Thanks in advance

Answer 1

If I got your requirement correctly, it could be expressed like this:

• The outer bool/should contains the two main cases:
  1. the current date now is in the interval [sale_start, sale_end] AND the sale_price is between the price bounds. Note that I've arbitrarily chosen the interval [1, 1000], but you can change that freely.
  2. the current date now is either before sale_start or after sale_end AND selling_price is in the [1, 1000] price range.

Query:

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "range": {
                  "sale_start": {
                    "lt": "now"
                  }
                }
              },
              {
                "range": {
                  "sale_end": {
                    "gt": "now"
                  }
                }
              },
              {
                "range": {
                  "sale_price": {
                    "gt": 1,
                    "lt": 1000
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "range": {
                  "sale_start": {
                    "gt": "now"
                  }
                }
              },
              {
                "range": {
                  "sale_end": {
                    "lt": "now"
                  }
                }
              }
            ],
            "must": [
              {
                "range": {
                  "selling_price": {
                    "gt": 1,
                    "lt": 1000
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}