Elasticsearch exclude top hit on field value

Question

{'country': 'France', 'collected': '2018-03-12', 'active': true}
{'country': 'France', 'collected': '2018-03-13', 'active': true}
{'country': 'France', 'collected': '2018-03-14', 'active': false}
{'country': 'Canada', 'collected': '2018-02-01', 'active': false}
{'country': 'Canada', 'collected': '2018-02-02', 'active': true}

Let's say I have this resultset, and I want to group them by country. After grouping them by country this will be the result:

{'country': 'France', 'collected': '2018-03-14', 'active': false}
{'country': 'Canada', 'collected': '2018-02-02', 'active': true}

But I want to exclude results where the last row active is false (the older rows of the same country can be true or false doesn't matter as long as the last row equals true), how can I do that in elasticsearch? Here is my query:

POST /test/_search?search_type=count
{
    "aggs": {
        "group": {
            "terms": {
                "field": "country"
            },
            "aggs": {
                "group_docs": {
                    "top_hits": {
                        "size": 1,
                        "sort": [
                            {
                                "collected": {
                                    "order": "desc"
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
}

Answer 1

I think you can get away with sorting by two fields in your top_hits: by active and by collected. Basically, you want trues to be first and when equal, then sort by collected. Something like the following will always show the active:true documents sorted by collected.

The only downside to this solution is that if you don't have any active documents, top_hits will show one active:false document.

{
  "size": 0,
  "aggs": {
    "group": {
      "terms": {
        "field": "country"
      },
      "aggs": {
        "group_docs": {
          "top_hits": {
            "size": 1,
            "sort": [
              {
                "active": {
                  "order": "desc"
                }, 
                "collected": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    }
  }
}