Range query inside another range query in ElasticSearch


(raj shah) #1

Hello,

I have two different range queries & I want to apply one range query within another range query.
But ES does not allow should query to be used in a range query.

Is there any other way to perform this query?
I am trying to replicate SQL's sub-query logic.

I have tried to use "MUST" for two different range queries but my range query contains lot of search ranges and the overall AND combination is very time-intensive.

The basic algorithm will be like:

 **Range Query 1**
{
         "SHOULD":
          {
            from: 10,
            to: 20,
                 AND
            _Range Query 2_
            {
                from:0,
                to:5
            }
          },
          {
            from: 30,
            to: 40,
               AND
            _Range Query 2_
            {
                from:0,
                to:5
            }
          }
    }

Regards,
Raj Shah


(Clinton Gormley) #2

I'm assuming your _Range Query 2_ is on a different field? You're heading down the right path, eg:

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "filter": [
              {
                "range": {
                  "foo": {
                    "gte": 10,
                    "lt": 20
                  }
                }
              },
              {
                "range": {
                  "bar": {
                    "gte": 0,
                    "lt": 5
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "filter": [
              {
                "range": {
                  "foo": {
                    "gte": 30,
                    "lt": 40
                  }
                }
              },
              {
                "range": {
                  "bar": {
                    "gte": 0,
                    "lt": 5
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

I'm also assuming that the second clause of 0..5 isn't going to be the same for every combination, otherwise you could arrange the logic differently, eg:

{
  "query": {
    "bool": {
      "filter": {
        "range": {
          "bar": {
            "gte": 0,
            "lt": 5
          }
        }
      },
      "should": [
        {
          "range": {
            "foo": {
              "gte": 10,
              "lt": 20
            }
          }
        },
        {
          "range": {
            "foo": {
              "gte": 30,
              "lt": 40
            }
          }
        }
      ]
    }
  }
}

(raj shah) #3

Hi @Clinton_Gormley,

Thank you for your response.

You are correct. RANGE_QUERY_2 is on a different field and the second clause(0...5) will also be different for every combination.
So, there will be NxN combinations which makes the query too long.
Is there any other way to optimize the above query from performance perspective?

And how will Elastic Search analyze the above query after applying filter context among two range queries?
Because as far as I know, if there is a query context that is executed first, then filter is applied on the results of the query context.

Thank you :slight_smile:


(Clinton Gormley) #4

See https://www.elastic.co/blog/elasticsearch-query-execution-order


(raj shah) #5

Hi @Clinton_Gormley,

Thanks for the link. That clears up a lot of doubts :slight_smile:

Regards,
Raj Shah


(system) #6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.