Rank over partition from postgresql in elasticsearch
You can achieve this using field collapsing clubbed with inner_hits.
{ "collapse": { "field": "user_id", "inner_hits": { "name": "order by created_at", "size": 1, "sort": [ { "created_at": "desc" } ] } },}Detailed Article: https://blog.francium.tech/sql-window-function-partition-by-in-elasticsearch-c2e3941495b6
It is simple: if you want to find the oldest record (for a given id), you just need the records for which no older ones (with the same id) exist. (this assumes that for a given id, no records exist with the same created_at date)
SELECT * FROM locations llWHERE NOT EXISTS ( SELECT * FROM locations nx WHERE nx.user_id = ll.user_id AND nx.created_at > ll.created_at );EDITED (it appears the OP wants the newst observation, not the oldest)
use top_hits.
"aggs": { "user_id": { "terms": {"field": "user_id"}, "aggs": { "top_location": { "top_hits": { "size": 1, "sort": { "created_at": "asc" }, "_source": [] } } } }}