Trouble with Elasticsearch nested query & date calculations

Question

I'm having trouble writing a query to query users with active events.

The short setup is I have users whom have events with start dates and end dates. Given a specific date, I need to know which users do NOT have active events on that day. Events are indexed as nested objects as they have their own models.

So here is some data

[
  {
    id: 1
    name: 'MyUser',
    events :[
       { id: 1, start: 02/01/2016, end: 02/05/2016 },
       { id: 2, start: 02/09/2016, end: 02/10/2016 },
    ]
  },
  {
    id: 2
    name: 'MyUser2',
    events :[
       { id: 3, start: 02/02/2016, end: 02/04/2016 },
    ]
  },
  {
    id: 3
    name: 'MyUser3',
    events :[
    ]
  }
]

the map looks like this

 'events' => [
            'type'=>'nested',
            'properties'=>[
                'start'=>[
                    'type' => 'date',
                    "format"=> "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd"
                ],
                'end'=>[
                    'type' => 'date',
                    "format"=> "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd"
                ]
            ]
        ],

So for a example query of 02/08/2016, i need to show all users free, 02/04/2016 only user 3, and 02/08/2016 only users 1 and 3

my query currently looks like this

{
    "filtered": {
        "filter": {
            "bool": {
                "should": [{
                    "term": {
                        "events_count": 0
                    }
                }, {
                    "nested": {
                        "path": "events",
                        "query": {
                            "bool": {
                                "must_not": [{
                                    "range": {
                                        "events.start": { 
                                          "lte" : "2016-02-08"
                                        }
                                    }
                                }, {
                                    "range": {
                                        "events.end": {
                                            "gte": "2016-02-08"
                                        }
                                    }
                                }]
                            }
                        }
                    }
                }]
            }
        }
    }
}

I indexed events_count separate because I already gave up on mixing missing with nested objects it just didn't work as expected

Actual Problem:

So the trouble with this is user trying to match start and end dates together, currently User1 is matching the start criteria lte $search_date when it shouldn't.

The logic I'm trying to write is WHEN events.start < $search_date AND events.end > $search_date, consider it a match.

What is actually happening it seems is its evaluating the start & end logic as separate logic and thus if start < $search_date even if .end < $search_date it considers it a match.

Answer 1

You need to wrap your range queries within another bool query and must clause (equivalent of SQL AND). must_not will exclude all the documents which match any of the queries

So rather than having

must_not => range_queries

make it like so:

must_not => bool => must => range_queries