In-Doc date comparison

I have an elastic index with thousands of such docs.

{
	Name: John Doe,
	FirstJobStartDate: 8/9/2016,
	FirstJobEndDate:1/4/2019,
	SecondJobStartDate:7/4/2019,
	SecondJobEndDate:8/8/2020,
	ThirdJobStartDate: 1/9/2020,
}

Except for Name & FirstJobStartDate, any other field is optional and may or may not be present in the doc.

I need to get 4 numbers:

  1. How many docs have a FirstJobEndDate?
    That's easy
{
	"size": 1,
	"query": {
		"filtered": {
			"filter": {
				"bool": {
					"must": [{
						"exists": {
							"field": "FirstJobEndDate"
						}
					}]
				}
			}
		}
	}
}

Now it gets complex:

  1. How many docs have a FirstJobEndDate that is lesser than the current date and they don't have EVEN ONE of (SecondJobStartDate, SecondJobEndDate or ThirdJobStartDate)?

  2. How many docs have a FirstJobEndDate, also have ANY ONE of (SecondJobStartDate, SecondJobEndDate, ThirdJobStartDate) and ANY ONE of those dates is within 1 Year of FirstJobEndDate?

  3. How many docs have a FirstJobEndDate, also have ANY ONE of (SecondJobStartDate, SecondJobEndDate, ThirdJobStartDate) and NONE of those dates is within 1 Year of FirstJobEndDate?

I believe this can be done with a correct mix of 'must' and 'should', but can't get any clear solution because of the comparison between two dates within the same document.

Just to confirm, all the dates are valid elastic date type fields and not strings.

Any help would be greatly appreciated.
Elastic version: 2.4

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