Term Aggregation and displaying top scored document for each term

[LGans316 (Ganesh)]

Hello Friends,

Request

Searching for 2 keys and displaying the aggregations alongside.

GET /new_irds_ld_tf/_search
{
  "size": 1,
  "query": {
    "bool": {
      "must": {
        "term": {
          "status.keyword": "active"
        }
      },
      "filter": {
        "terms": {
          "AltID.ISIN.keyword": [
            "DE000A0Z3CW9",
            "KR4157M60003"
          ]
        }
      }
    }
  },
  "sort": [
    {
      "AltID.ISIN.keyword": {
        "order": "asc"
      }
    },
    "_score"
  ],
  "aggs": {
    "AltID": {
      "terms": {
        "field": "AltID.ISIN.keyword"
      }
    }
  }
}

Response

{
  "took": 6,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 17,
    "max_score": null,
    "hits": [
      {
        "_index": "new_irds_ld_tf",
        "_id": "ld_tf_145066457",
        "_score": 0.50210315,
        "_source": {
          "AltID": {
            "ISIN": "DE000A0Z3CW9",
          },
          "RelAltID": {
            "rel_RIC": ".V2TX"
          },
          "status": "active"
        },
        "sort": [
          "DE000A0Z3CW9",
          0.50210315
        ]
      }
    ]
  },
  "aggregations": {
    "AltID": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "DE000A0Z3CW9",
          "doc_count": 16
        },
        {
          "key": "KR4157M60003",
          "doc_count": 1
        }
      ]
    }
  }
}

As per the response above, there are 16 docs matching "key": "DE000A0Z3CW9" and 1 doc matching "key": "KR4157M60003".

Here I would like the response to contain the first top-score document for each of these keys (i.e. one doc with the highest score for key DE000A0Z3CW9 and one doc with the highest score for key KR4157M60003).

Please advice.

[Mark_Harwood]

@LGans316

the first top-score document for each of these keys

Use a top_hits aggregation with the size set to 1

[LGans316 (Ganesh)]

Thanks Mark but due to some requirement changes, the request query has become like this:

Request

GET /new_irds_*/_search?filter_path=aggregations
{
  "size": 0,
  "query": {
    "bool": {
      "must": {
        "term": {
          "status.keyword": "active"
        }
      },
      "filter": {
        "terms": {
          "AltID.ISIN.keyword": [
            "N.A."
          ]
        }
      }
    }
  },
  "aggs": {
    "AltID": {
      "terms": {
        "field": "AltID.ISIN.keyword"
      },
      "aggs": {
        "IndexName": {
          "terms": {
            "field": "_index"
          }
        }
      }
    }
  }
}

Response

{
  "aggregations": {
    "AltID": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "N.A.",
          "doc_count": 147,
          "IndexName": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": "new_irds_ld_to",
                "doc_count": 105
              },
              {
                "key": "new_irds_ld_tf",
                "doc_count": 42
              }
            ]
          }
        }
      ]
    }
  }
}

In each bucket, I would like top scored doc to be displayed which I am unable to figure out.

[LGans316 (Ganesh)]

Any help would be appreciated as I am unable to figure out how to retrieve the top scored doc grouping by fieldname, indexName.

[LGans316 (Ganesh)]

Still searching for answers ....

[Mark_Harwood]

You nest the top_hits aggregation under the IndexName agg exactly the same way that IndexName is nested under the containingAltID aggregation. It's just layers of nested JSON to represent the tree of aggregations you want.

[LGans316 (Ganesh)]

Wow. Thanks a lot @Mark_Harwood . Looking good now and looks like I simply wasn't paying attention earlier. Thank you so much.

Working Request

GET /new_irds_*/_search?filter_path=aggregations
{
  "size": 0,
  "query": {
    "bool": {
      "must": {
        "term": {
          "status.keyword": "active"
        }
      },
      "filter": {
        "terms": {
          "AltID.ISIN.keyword": [
            "N.A.",
            "DE000A0G9C70"
          ]
        }
      }
    }
  },
  "aggs": {
    "AltID": {
      "terms": {
        "field": "AltID.ISIN.keyword"
      },
      "aggs": {
        "IndexName": {
          "terms": {
            "field": "_index"
          },
          "aggs": {
            "top_hits": {
              "top_hits": {
                "sort": [
                  {
                    "_score": {
                      "order": "desc"
                    }
                  }
                ],
                "size": 1
              }
            }
          }
        }
      }
    }
  }
}