Issues with complex range query

[teemukarvinen]

Hi,

I have documents that contain array of allocation for person.

Mapping:

"Allocations": {
  "properties": {
    "endDate": {
      "type": "date"
    },
    "startDate": {
      "type": "date"
    },
    "state": {
      "type": "long"
    },
    ...
  },
}

I am using the Javascript client to interact with Elasticsearch.

I need to get all people that don't have allocations between X and Y dates and that the state is 1.

If state is 0 they can be seen as not active. State can only be 1 or 0.

We have search in our system were the user can input the X and Y dates, and based on that we need to give back people that are free between those dates.

I tried to use range to check that startDate is not between X and Y:

must: [
  bool: {
    must_not: [
      {
        range: {
          "Allocations.startDate": {
            "gt": x,
            "lt": y
          }
        }
      }
    ]
  }
]

The issue is that it then doesn't allow any allocation to be greater than X, even if the allocations dates are next year.

The allocations array can also be empty.

I'm trying to improve our systems search performance by moving from traditional database queries to Elasticsearch and this is one of the only issues I have faced.

There is also a lot more to this search than just allocation dates but they all work fine already. Person can have multiple CVs that all the other parts of the queries looks for. System gets different type of tokens and based on those tokens the query is generated dynamically.

Can I get some help with this?

-Teemu