Understanding Aggregation results on ElasticSearch

Question

I am working on a specific query in ElasticSearch. The goal of the query is to return all the unique results with latest timestamps. So just to give the background, in the elasticsearch DB, there can be mulitple entries for each of these unique field "x" with different timestamps. I want the ES query to return the latest timestamps for each of these unique field x. So data looks like in the ES database:

{"x" : "1", "time": 1536574915}
{"x" : "2", "time": 1536574919}
{"x" : "1", "time": 1536574815}
{"x" : "2", "time": 1536574819}
{"x" : "3", "time": 1536574915}
{"x" : "4", "time": 1536574915}

Expected output is

{"x" : "1", "time": 1536574915}
{"x" : "2", "time": 1536574919}
{"x" : "3", "time": 1536574915}
{"x" : "4", "time": 1536574915}

The query I am currently using is:

{
            "size": 0,
            "query": {
                "bool": {
                    "must": [],
                    "filter": {
                        "range": {
                            "time": {
                                "lte": "2019-11-16", Can give epoch conversion here
                                "format": "date_optional_time"
                                }
                            }
                        }
                    }
                },
            "aggs": {
                "group_by": {
                 "terms": {
                        "field": "x"
                        },
                    "aggs": {
                        "resource": {
                            "terms": {
                                "field": "time",
                                "size": 1,
                                "order": {
                                    "_key": "desc"
                                }

                            },

                            "aggs": {
                                "include_source": {
                                    "top_hits": {
                                        "from": 0,
                                        "size": 1,
                                        "_source": {}
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

The results that get returned on above query have

[
    {
        "_scroll_id": "DnF1ZXJ5VGhlbkZldGNoAgAAAAAAAAECFmtnNUY4dHFKUXVldXdQMkNSaE1femcAAAAAAAABAxZrZzVGOHRxSlF1ZXV3UDJDUmhNX3pn",
        "took": 227,
        "timed_out": false,
        "_shards": {
            "total": 2,
            "successful": 2,
            "skipped": 0,
            "failed": 0
        },
        "hits": {
            "total": 343533,
            "max_score": 0.0,
            "hits": [
                {
                }
            ]
},
   "aggregations": {
                "group_by": {
                    "doc_count_error_upper_bound": 4,
                    "sum_other_doc_count": 343513,
                    "buckets": [
                  { # here is the actual data.
                  }
           ]
        }
      }
    },
    {
       #another scroll_id. Removed the data as its huge.
   }
]

My question is, where are the unique results present in the above case? is it within [hits][hits] or are they within "aggregation"? if within aggregation, for a million records, aggregation is returning me only 10 results. and If I depend on [hits][hits] from each scroll list, then the results are repetitive. I am trying to understand, which part of this result can I get the correct unique entries based on my above query constraint. Or is the query incorrectly formed or missing some parameter. Appreciate any help. Thanks.

Answer 1

Your aggregation is not correct since you're retrieving the top hits for each x and time, while your goal is to retrieve the latest hit for each x. You need to modify your query like below, i.e. you only aggregate by x and in your top_hits sub-aggregation you sort documents by decreasing time and only take the last one.

{
  "size": 0,
  "aggs": {
    "group_by": {
      "terms": {
        "field": "x"
      },
      "aggs": {
        "resource": {
          "top_hits": {
            "from": 0,
            "size": 1,
            "sort": {
              "time": "desc"
            },
            "_source": {}
          }
        }
      }
    }
  }
}

The documents you're looking for are in the resource.hits.hits section of each of your buckets:

  "aggregations" : {
    "group_by" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "1",
          "doc_count" : 2,
          "resource" : {
            "hits" : {
              "total" : 2,
              "max_score" : null,
              "hits" : [
                {
                  "_index" : "times",
                  "_type" : "doc",
                  "_id" : "PZt7G2cBJos57mIu0oy-",
                  "_score" : null,
                  "_source" : {
                    "x" : "1",
                    "time" : 1536574915
                  },
                  "sort" : [
                    1536574915
                  ]
                }
              ]
            }
          }
        },
        {
          "key" : "2",
          "doc_count" : 2,
          "resource" : {
            "hits" : {
              "total" : 2,
              "max_score" : null,
              "hits" : [
                {
                  "_index" : "times",
                  "_type" : "doc",
                  "_id" : "Ppt7G2cBJos57mIu0oy-",
                  "_score" : null,
                  "_source" : {
                    "x" : "2",
                    "time" : 1536574919
                  },
                  "sort" : [
                    1536574919
                  ]
                }
              ]
            }
          }
        },
        {
          "key" : "3",
          "doc_count" : 1,
          "resource" : {
            "hits" : {
              "total" : 1,
              "max_score" : null,
              "hits" : [
                {
                  "_index" : "times",
                  "_type" : "doc",
                  "_id" : "QZt7G2cBJos57mIu0oy-",
                  "_score" : null,
                  "_source" : {
                    "x" : "3",
                    "time" : 1536574915
                  },
                  "sort" : [
                    1536574915
                  ]
                }
              ]
            }
          }
        },
        {
          "key" : "4",
          "doc_count" : 1,
          "resource" : {
            "hits" : {
              "total" : 1,
              "max_score" : null,
              "hits" : [
                {
                  "_index" : "times",
                  "_type" : "doc",
                  "_id" : "Qpt7G2cBJos57mIu0oy-",
                  "_score" : null,
                  "_source" : {
                    "x" : "4",
                    "time" : 1536574915
                  },
                  "sort" : [
                    1536574915
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  }