ElasticSearch query date capability scripting

Question

A little background....I'm converting an existing API to leverage elastic search for performance gains.

It's a rails app that's using active record. Here is the query where condition that we're trying to do in elasticsearch:

where("start_date BETWEEN :start_at AND :end_at
       OR end_date BETWEEN :start_at AND :end_at
       OR :start_at BETWEEN start_date AND end_date
       OR :end_at BETWEEN start_date and end_date", start_at: start_at.to_date, end_at: end_at.to_date)

The first half of that where clause is easy to replicate. Here's how that part looks:

Model.search(
  query:  {
    bool: {
      should: [
        {
          range: {
            start_date: {
              gte: '2015-12-01',
              lte: '2015-12-25'
            }
          }
        },
        {
          range: {
            end_date: {
              gte: '2015-12-01',
              lte: '2015-12-25'
            }
          }
        }
      ],
      minimum_should_match: 1
    }
  }
)

I'm just not sure how to implement the 2nd part of that where clause. I've attempted using scripting but I'm having issues converting the dates to the proper formats so that they can be compared.

For example here was something I tried:

filter: {
  script: {
    script: {
      inline: "doc['start_date'] > start_on",
      params: {
        start_on: Date.parse('2015-12-01')
      }
    }
  }
}

When I do this I get this error:

Cannot compare org.elasticsearch.index.fielddata.ScriptDocValues$Longs with value '[1449446400000]' and java.lang.String with value '2015-12-01'"

I've also tried this:

script: "Date.parse('yyyy-MM-dd', '2015-12-01').getTime() >= doc['start_date']"

I get this error:

org.elasticsearch.index.fielddata.ScriptDocValues$Longs cannot be cast to java.lang.Long

I'm just not sure how to get the data types to match so that I can compare the dates.

I wish I could do something like this, but its not possible of course:

range: {
  '2015-12-01': {
    gte: start_date
  }
}

Any help on this would be greatly appreciated. Thanks!

score 0 · Answer 1 · answered Feb 11 '16 at 17:50

0

Try this:

filter: {
  script: {
   script: {
     inline: "new java.util.Date(doc['start_date'].value) > new java.util.Date(2015-12-01)"

    }
  }
}

answered Feb 11 '16 at 17:50

Richa

7,419
6
25
34

Thanks, those date conversions worked. I ran into other issues though because of the should conditions. The ideal solution is to 'OR' all those date related conditions together, so even if the script evaluated to true, the minimum_should_match logic would reject the document. – Eugene Correia Feb 11 '16 at 20:59

score 0 · Answer 2 · edited May 23 '17 at 12:15

A co-worker sent me this: https://stackoverflow.com/a/325964, so I ended up with a much simpler approach:

Model.search(
  query:  {
    bool: {
      must: [
        {
          range: {
            start_date: {
              lte: '2015-12-25'
            }
          }
        },
        {
          range: {
            end_date: {
              gte: '2015-12-01',
            }
          }
        }
      ]
    }
  }
)

ElasticSearch query date capability scripting

2 Answers2