ElasticSearch - Filter Nested Aggregation
This is a partial answer.
There's one main problem: according to your data, there is actually NO document that would match your requirements, so I added some:
curl -XPUT 'localhost:9200/my_index/reporting/9' -d '{ "events": [ { "name": "INSTALL", "date": "2014-11-03" } ]}'curl -XPUT 'localhost:9200/my_index/reporting/10' -d '{ "events": [ { "name": "INSTALL", "date": "2014-11-03" }, { "name": "UNINSTALL", "date": "2014-11-01" } ]}'To be able to apply the logic, I changed the Schema so that the events are also included in the parent - that way you can search for "doesn't have any UNINSTALL events". Because the thing is, in a nested search, you're always just looking at ONE single event, so you can't do any kind of "reporting-wide" searches.
curl -XPUT 'localhost:9200/my_index' -d '{ "mappings": { "reporting": { "properties": { "events": { "type": "nested", "include_in_root": true, "properties": { "name": { "type": "string", "index" : "not_analyzed" }, "date": { "type": "date" } } } } } }}'And now to the query itself. It seems that when using a nested filter, you can't go directly to the "filter". You must first do the "query > filtered > filter" thing.
One tip for writing long elasticsearch queries in general - remembering that you have "and" and "or" operators aside from "must" and "must_not" - is to just write it out code-like. In your case:
has_one(event.name == 'INSTALL' && event.date >= '2014-11-02')&& has_none(event.name == 'UNINSTALL') && has_none(event.name == 'UNINSTALL' && event.date >= '2014-11-02')Or:
has_one(event.name == 'INSTALL' && event.date >= '2014-11-02')&& ( has_none(event.name == 'UNINSTALL') || has_only(event.name == 'UNINSTALL' && event.date >= '2014-11-02') )I was able to apply all but the last has_only / has_none. For that, you might want to try using child-documents. There you can at least use the has_child filter under a must_not bool.
The current query:
GET /my_index/reporting/_search{ "query": { "filtered": { "query": { "match_all": {} }, "filter": { "and": { "filters": [ { "or": { "filters": [ { "bool": { "must_not": [ { "term": { "events.name": "UNINSTALL" } } ] } }, { "nested": { "path": "events", "query": { "filtered": { "filter": { "bool": { "must": [ { "term": { "name": "UNINSTALL" } }, { "range": { "date": { "lt": "2014-11-02" } } } ] } } } } } } ] } }, { "nested": { "path": "events", "query": { "filtered": { "filter": { "bool": { "must": [ { "term": { "name": "INSTALL" } }, { "range": { "date": { "gte": "2014-11-02" } } } ] } } } } } } ] } } } }, "aggregations": { "filtered_result": { "filter": { "and": { "filters": [ { "or": { "filters": [ { "bool": { "must_not": [ { "term": { "events.name": "UNINSTALL" } } ] } }, { "nested": { "path": "events", "query": { "filtered": { "filter": { "bool": { "must": [ { "term": { "name": "UNINSTALL" } }, { "range": { "date": { "lt": "2014-11-02" } } } ] } } } } } } ] } }, { "nested": { "path": "events", "query": { "filtered": { "filter": { "bool": { "must": [ { "term": { "name": "INSTALL" } }, { "range": { "date": { "gte": "2014-11-02" } } } ] } } } } } } ] } }, "aggs": { "result": { "nested": { "path": "events" }, "aggs": { "NAME": { "terms": { "field": "date", "format": "yyyy-MM-dd", "order": { "_term": "asc" } } } } } } } }}