4

I've two questions related to ElasticSearch.

1) Is there any way to specify that I want results with specific field sorted in descending order? An equivalt SQL query will be: 

select * from table1 where a="b" order by myprimarykey desc;

2) How to get first and last(latest) record?

Abhijeet Pathak
  • 1,948
  • 3
  • 20
  • 28

1 Answers1

10

1) Elasticsearch has quite sophisticated Sorting API that allows you to control sort order. So, in elasticsearch, an equivalent to your MySql query would look like this:

{
    "query" : {
        "term" : { "a" : "b" }
    },
    "sort" : [
        { "myprimarykey" : "desc"} }
    ]
}

Sorting can also be specified on _search URI.

2) To retrieve the first and the last records you would need to perform two searches with desc and asc sort orders and retrieve one record for each. It's possible to combine both queries using Multi Search API.

imotov
  • 28,277
  • 3
  • 90
  • 82
  • Regarding 2nd point, I have tried using "desc" with "match all" query. But the problem is that the "hits" in the result shows large record count, not 1. Does that mean even if I want 1 record, all the records are being scanned and one is being returned? I need only one first and/or last record (using separate queries for both ofcourse). – Abhijeet Pathak Jul 26 '12 at 07:46
  • The large record count is the number of records that your "match all" query has found. So, yes, it finds all records and retrieves only one, but it doesn't mean it does full table scan in SQL terms. It's quite lightweight process. – imotov Jul 26 '12 at 13:32
  • Ok. One last thing.. Will the performance get affected for above query if I have few million records in index? – Abhijeet Pathak Jul 28 '12 at 08:33
  • Performance will be affected, it always does get affected as the number of records grows, but it might be still pretty fast. Will it be fast enough for your use case is hard to tell. It depends on your requirements, and type of your sort key. I would suggest testing it with your data. – imotov Jul 29 '12 at 01:54