I have a wildcard index (let's call it foo-*) that gets created once per day. So for example, I have the following indexes now that I search across:
foo-2019-08-23
foo-2019-08-24
foo-2019-08-25
foo-2019-08-26
One field in my document is a keyword field called name and another is a boolean field called active. I need to be able to search for all the documents with the same name but filter based on the active field in the most recent document. Is this possible?
For example, let's say I have the following documents:
{"@timestamp": "2019-08-23T10:00:00.000Z", "name": "foo", "active": false}
{"@timestamp": "2019-08-24T23:00:00.000Z", "name": "foo", "active": false}
{"@timestamp": "2019-08-25T14:00:00.000Z", "name": "foo", "active": true}
If I wanted to search for all documents with the name foo, but only when foo is currently not active, then in the above case my search would return nothing. However, if I wanted all the foo documents when foo is currently active, then in the above case my search would return all 3 documents.
Please advise. Thank you in advance!
EDIT
My ultimate goal is to be able to aggregate documents no matter what the current value is for active, then only return aggregated results for ones that should be considered active based on the most recent document. Perhaps a different example will explain my use case a little better.
{"@timestamp": "2019-08-23T10:00:00.000Z", "name": "foo", "active": false, "count": 5}
{"@timestamp": "2019-08-24T23:00:00.000Z", "name": "foo", "active": false, "count": 6}
{"@timestamp": "2019-08-25T14:00:00.000Z", "name": "foo", "active": true, "count": 7}
{"@timestamp": "2019-08-23T09:00:00.000Z", "name": "bar", "active": false, "count": 2}
{"@timestamp": "2019-08-24T22:00:00.000Z", "name": "bar", "active": true, "count": 3}
{"@timestamp": "2019-08-25T13:00:00.000Z", "name": "bar", "active": false, "count": 4}
Given the above two sets of documents, one for foo and one for bar, assume I did a match all search, doing a sum aggregation on the count field, grouping by name. I would end up with aggregation results like the following:
{"name": "foo", "count": 18}
{"name": "bar", "count": 9}
This is a simple query. The one I'm trying to figure out how to do is to limit the aggregated results returned based on the active field in the most recent document for a given name value. As an example, lets say I wanted to do the above, but only see results for a group who's most recent document has active: false. In this case, I'd want to have the following returned to me:
{"name": "bar", "count": 9}
I briefly considered using active as a filter, but that would mess up the results of the count aggregation.
To reiterate, I want to aggregate over all documents, grouped by a field (in this case name), but only return certain aggregated results based on the active value of the most recent document in each group.