Conditional Sorting in ElasticSearch

Question

I have some documents that I would like to sort on a date field. For documents with date equal to a specified date, example today, and all dates after that I would like to sort ascending. For dates before the specified date I would like to sort in descending order.

Is this possible in ElasticSearch? If so could you suggest any literature or an approach.

date is of type "date" and format "dateOptionalTime".

Thanks

Answer 1

Yes this is possible in ElasticSearch using a script, either for sorting or for scoring.

My preference would be for a scoring script because 'script based score' is going to be quicker (according to the documentation).

Using a scoring script, you could use the Unix timestamp for the date field of type int/long and an mvel sorting script in the custom_score query. You might need to re-index your documents. You would also need to be able to convert the searched for time into a Unix timestamp to pump it at ElasticSearch.

The sorting script would then deduct the requested timestamp from each document's timestamp and make an absolute value. Then the results are sorted in ascending order - the lowest 'distance' is the best.

So when looking for documents dated about a year ago, it would look something like:

"query": {
    "custom_score" : {
        "query" : {
            ....
        },
        "params" : {
            "req_date_stamp" : 1348438345,
        },
        "script" : "abs(doc['timestamp'].value - req_date_timestamp)"
    }
},
"sort": {
    "_score": {
        'order': 'asc'
    }
}

(Apologies for any mistakes in my JSON - I tested this idea in pyes)

You might need to tweak this to get the rounding right - for example your question mentions matching days, so you might want to round the timestamp generator to the nearest day.

For "full" info you can check out the Custom Score Query docs and follow the link to MVEL scripting.