Return the most recent record from ElasticSearch index

I would like to return the most recent record (top 1) from ElasticSearch index similar to the sql query below;

SELECT TOP 1 Id, name, title 
FROM MyTable 
ORDER BY Date DESC;

Can this be done?


Do you have _timestamp enabled in your doc mapping?

{
    "doctype": {
        "_timestamp": {
            "enabled": "true",
            "store": "yes"
        },
        "properties": {
            ...
        }
    }
}

You can check your mapping here:

http://localhost:9200/_all/_mapping

If so I think this might work to get most recent:

{
  "query": {
    "match_all": {}
  },
  "size": 1,
  "sort": [
    {
      "_timestamp": {
        "order": "desc"
      }
    }
  ]
}

For information purpose, _timestamp is now deprecated since 2.0.0-beta2. Use date type in your mapping.

A simple date mapping JSON from date datatype doc:

{
  "mappings": {
     "my_type": {
        "properties": {
          "date": {
          "type": "date" 
        }
      }
    }
  }
}

You can also add a format field in date:

{
  "mappings": {
    "my_type": {
      "properties": {
        "date": {
          "type":   "date",
          "format": "yyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
        }
      }
    }
  }
}