GET latest value for each unique value?

Elastic Stack Elasticsearch
1

Hi,

I have the query below which will provide me with the latest error for a certain location. However I have multiple locations and I would like to pull the latest error for each location (without having to put in a whole list of locations) with only one query. What would the best way to do that?

{
"query": {
    "bool": {
      "must": [
        {
                "exists": {
                    "field": "error"
                }
        },
        {
          "term": {
            "location": "locationA" 
          }
        }
      ]
    }
  },
    "size": 1,
  "sort": [
    {
      "@timestamp": {
        "order": "desc"
      }
    }
  ]
}
2

I think you will need an aggregation for this.
Which es version are you using?

Sinc es 5.5? there is a Top Hit aggregation that i think does what you have in mind.
https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html

3

Doesn't look very easy to understand...

I'm on 6.2.

1 Like
4

here is a query which hopefully works for your example
under the key "aggregations" is the grouped view and not under "hits"

{
  "size": 0,
  "aggs": {
    "2": {
      "terms": {
        "field": "location",
        "size": 150,
        "order": {
          "_term": "asc"
        }
      },
      "aggs": {
        "1": {
          "top_hits": {
            "docvalue_fields": [
              "error"
            ],
            "_source": "error",
            "size": 1,
            "sort": [
              {
                "@timestamp": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    }
  }
}
5

Thanks. That example is a lot easier to read. I will get to work with it.

Just to make sure I understand what is going on:

"size": 150,

Is the maximum number of results? E.g. if I had more than 150 locations I would need to change it to whatever amount of locations I have?

"_term": "asc"

Orders the results alphabetically?

"_source": "error"

The agg sets the value as _source?

6

yes that is correct

also correct

Answers: Which fields of the document with the highest timestamp should be in the response?
under "_source" are defined the fields which should be shown in the hits _source field
and under "docvalue_fields" are defined the fields which should be shown under a "fields" field in the hit

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.