Elastic Search Query | Aggregation | Nested Aggregation

Question

I have this as one of the hit inside hits while querying all data

{
  "id": "id123",
  "sc": 1,
  "src": {
    "aid": "aid123",
    "result": {      
      "cid": "cid123",
      "scm": "GL",
      "title": "DTitle",
      "reason": "DReason",
      "status": "Failed",
      "entity": "DEnt"
    },
    "createdAt": 1718864490635,
    "rel": {
      "nam": "dRel",
      "par": "pid123"
    }
  }
}

1. I want to aggregate them first on basis of cid,aid and retrieve latest entry per group
2. Then i need to aggregate on cid on the evaluated result from prev query and find total counts, passed and failed count based on status along with all field inside result

GET /index/_search
{
  "size" : 0,
  "aggs": {
    "compliance_group": {
      "terms": {
        "field": "result.cid"
      },
      "aggs": {
        "artifact_group": {
          "terms": {
            "field": "aid"
          },
          "aggs": {
            "latest_execution": {
              "top_hits": {
                "sort": [
                  {
                    "createdAt": {
                      "order": "desc"  // Use "asc" for ascending order
                    }
                  }
                ],
                "_source": {
                  "includes": [
                    "result.cid", 
                    "aid", 
                    "result.scm",
                    "result.title",
                    "result.reason",
                    "result.entity",
                    "result.status"
                  ]
                },
                "size": 1
              }
            }
          }
        }
      }
    }
  }
}

GET /index/_search
{
  "size": 0,
  "aggs": {
    "by_complianceId": {
      "terms": {
        "field": "result.cid.keyword"
      },
      "aggs": {
        "total_count": {
          "value_count": {
            "field": "result.cid.keyword"
          }
        },
        "passed_count":{
          "filter": {
            "term": {
              "result.status.keyword": "PASSED"
            }
          }
        },
        "failed_count":{
          "filter": {
            "term": {
              "result.status.keyword": "FAILED"
            }
          }
        },
         "sample_docs": {
          "top_hits": {
            "size": 1,
            "_source": {
                  "includes": [
                    "result.cid", 
                    "aid", 
                    "result.scm",
                    "result.title",
                    "result.reason",
                    "result.entity",
                    "result.status"
                  ]
              }
          }
        }
      }
    }
  }
}

Both the queries are working fine in separation , how do i use the second aggregation on the evaluated result from first aggregation

Answer 1

There is no way to use Elasticsearch query results in the same query.

Elasticsearch does not support chaining the results of one aggregation directly into another aggregation within a single query. You should handle with it in application side.