How to remove hits after aggregation

Elastic Stack Elasticsearch
1

Hello,
I'm trying to do an aggregate query to get issues at their highest state but if the highest state is 'closed' then I want to completely ignore the issue. For example,

docId	    action		issue
Doc1		raised		issue1
Doc2		solved		issue1
Doc3		raised		issue2
Doc4		solved		issue2
Doc5		closed		issue1
Doc6		raised		issue3

I want the users to see all issues at their highest state but if the highest state is closed I want to hide the issue completely.

Expected result:

Id		    action		issue
Doc4		solved		issue2
Doc6		raised		issue3

Can this be done?
Anyhelp is highly appreciated.
Thank you.

2

May be an idea would be something like:

DELETE test
PUT test
{
  "mappings": {
    "properties": {
      "action": {
        "type": "keyword"
      },
      "issue": {
        "type": "keyword"
      }
    }
  }
}
PUT test/_doc/Doc1
{
  "action": "1-raised", "issue": "issue1"
}
PUT test/_doc/Doc2
{
  "action": "2-solved", "issue": "issue1"
}
PUT test/_doc/Doc3
{
  "action": "1-raised", "issue": "issue2"
}
PUT test/_doc/Doc4
{
  "action": "2-solved", "issue": "issue2"
}
PUT test/_doc/Doc5
{
  "action": "3-closed", "issue": "issue1"
}
PUT test/_doc/Doc6
{
  "action": "1-raised", "issue": "issue3"
}

GET /test/_search?filter_path=aggregations.issue.buckets.key,aggregations.issue.buckets.action.buckets.key
{
 "size": 0,
 "aggs": {
   "issue": {
     "terms": {
       "field": "issue"
     },
     "aggs": {
       "action": {
         "terms": {
           "field": "action",
           "order": {
             "_key": "desc"
           },
           "size": 1
         }
       }
     }
   }
 }
}

Which produces:

{
  "aggregations": {
    "issue": {
      "buckets": [
        {
          "key": "issue1",
          "action": {
            "buckets": [
              {
                "key": "3-closed"
              }
            ]
          }
        },
        {
          "key": "issue2",
          "action": {
            "buckets": [
              {
                "key": "2-solved"
              }
            ]
          }
        },
        {
          "key": "issue3",
          "action": {
            "buckets": [
              {
                "key": "1-raised"
              }
            ]
          }
        }
      ]
    }
  }
}

But you then need to manually exclude the first one as it contains 3-closed.

At least, it's a "start"... :wink:

3

Thank you for trying :slight_smile:
Unfortunately, I need a one-step solution.

4

Hello again!
I found this on stackoverflow. It's very similar to the situation I am in.

This solved my problem. I was also able to include the top hit inside the bucket with the top_hits aggregation.

5

Great. Would you mind sharing your final example, in the same way I did? So we can mark this as solved with a nice solution people can run later.

6

Unfortunately, I can not share my elastic query due to company restrictions. But it is similar to this: kibana - is there any way to apply post filter in Elasticsearch on only aggregated result on on all the document? - Stack Overflow