Get newest value per distinct field

[gudmundur]

Greetings

I want to do a query which returns the newest 1 value per distinct combination of specific underlying fields (maybe also after a certain timestamp)

In the sql world I can do a query like this:

select Element, Pot, Quantity from Quantities where Id in (
select max(Id) from Quantities group by Element, Pot)
order by  Element, Pot
where timestamp > '2020-06-01'

The effect of this would be if I had lets say this data:

Time, Element, Pot, Quantity
2020-06-02, Fe, 1, 100
2020-06-03, Fe, 1, 90
2020-06-03, Fe, 2, 85
2020-06-02, Cu, 1, 100
2020-06-03, Cu, 1, 90

I would get something like this back:

Fe, 1, 90
Fe, 2, 85
Cu, 1, 90

How would I do a similar query in ElasticSearch?

Thank you

[mayya (Mayya Sharipova)]

You can obtain these results using aggregations.
You can do a terms aggregation on Element field, inside it as a sub-aggregation another terms aggregation on Pot field, and inside Pot aggregation another sub-aggregation of type top hits. This top hits aggregation can request just a single last document for each distinct element and pot:

                    "top_hits": {
                        "sort": [
                            {
                                "time": {
                                    "order": "desc"
                                }
                            }
                        ],
                        "size" : 1
                    }

[gudmundur]

Thank you Mayya!

For those who might need to solve a similar problem, this is what I ended up with as an equivalent to the sql query above:

GET index/_search
{
  "size": 0,
  "aggs": {
    "date_ranged": {
      "date_range": {
        "field": "@timestamp",
        "ranges": [
          {
            "from": SOME_DATE_HERE,
            "to": "now"
          }
        ]
      },
      "aggs": {
        "elements": {
          "terms": {
            "field": "Element.keyword"
          },
          "aggs": {
            "pots": {
              "terms": {
                "field": "Pot"
              },
              "aggs": {
                "top_results": {
                  "top_hits": {
                    "sort": [{
                      "@timestamp": {
                        "order": "desc"
                      }
                    }],
                    "size": 1
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}