CODEWITHSUNDEEP
elasticsearch
Yasir
Yasir

Reputation: 4767

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?

Upvotes: 67

Views: 155420

Answers (8)

SergeyB
SergeyB

Reputation: 9848

Since this question was originally asked and answered, some of the inner-workings of Elasticsearch have changed, particularly around timestamps. Here is a full example showing how to query for single latest record. Tested on ES 6/7.

1) Tell Elasticsearch to treat timestamp field as the timestamp

curl -XPUT "localhost:9200/my_index?pretty" -H 'Content-Type: application/json' -d '{"mappings":{"message":{"properties":{"timestamp":{"type":"date"}}}}}'

2) Put some test data into the index

curl -XPOST "localhost:9200/my_index/message/1" -H 'Content-Type: application/json' -d '{ "timestamp" : "2019-08-02T03:00:00Z", "message" : "hello world" }'
curl -XPOST "localhost:9200/my_index/message/2" -H 'Content-Type: application/json' -d '{ "timestamp" : "2019-08-02T04:00:00Z", "message" : "bye world" }'

3) Query for the latest record

curl -X POST "localhost:9200/my_index/_search" -H 'Content-Type: application/json' -d '{"query": {"match_all": {}},"size": 1,"sort": [{"timestamp": {"order": "desc"}}]}'

4) Expected results

{
   "took":0,
   "timed_out":false,
   "_shards":{
      "total":5,
      "successful":5,
      "skipped":0,
      "failed":0
   },
   "hits":{
      "total":2,
      "max_score":null,
      "hits":[
         {
            "_index":"my_index",
            "_type":"message",
            "_id":"2",
            "_score":null,
            "_source":{
               "timestamp":"2019-08-02T04:00:00Z",
               "message":"bye world"
            },
            "sort":[
               1564718400000
            ]
         }
      ]
   }
}

Upvotes: 6

Alberto Rodriguez
Alberto Rodriguez

Reputation: 31

I used @timestamp instead of _timestamp

{
    'size' : 1,
    'query': {
        'match_all' : {}
            },
    "sort" : [{"@timestamp":{"order": "desc"}}]
}

Upvotes: 3

Trevor Boyd Smith
Trevor Boyd Smith

Reputation: 19253

If you are using python elasticsearch5 module or curl:

  1. make sure each document that gets inserted has
    • a timestamp field that is type datetime
    • and you are monotonically increasing the timestamp value for each document

  2. from python you do 

    es = elasticsearch5.Elasticsearch('my_host:my_port')
es.search(
    index='my_index', 
    size=1,
    sort='my_timestamp:desc'
    )

If your documents are not inserted with any field that is of type datetime, then I don't believe you can get the N "most recent".

Upvotes: 4

Jayaram Venkat
Jayaram Venkat

Reputation: 365

Get the Last ID using by date (with out time stamp)

Sample URL : http://localhost:9200/deal/dealsdetails/
Method : POST

Query :

{
  "fields": ["_id"],
  "sort": [{
      "created_date": {
        "order": "desc"
      }
    },
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "size": 1
}

result:

{
  "took": 4,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 9,
    "max_score": null,
    "hits": [{
      "_index": "deal",
      "_type": "dealsdetails",
      "_id": "10",
      "_score": 1,
      "sort": [
        1478266145174,
        1
      ]
    }]
  }
}

Upvotes: 12

SandTh
SandTh

Reputation: 2397

the _timestamp didn't work out for me,

this query does work for me:

(as in mconlin's answer)

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

Could be trivial but the _timestamp answer didn't gave an error but not a good result either...

Hope to help someone...

(kibana/elastic 5.0.4)

S.

Upvotes: 0

Adrien Chaussende
Adrien Chaussende

Reputation: 361

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"
        }
      }
    }
  }
}

Upvotes: 15

mconlin
mconlin

Reputation: 8733

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"
      }
    }
  ]
}

Upvotes: 90

dadoonet
dadoonet

Reputation: 14492

You can use sort on date field and size=1 parameter. Does it help?

Upvotes: 7

Related Questions