Conditional Sorting in ElasticSearch

For this kind of specific use cases, you should use a sorting script.

See the "script based sorting" section in the Sort documentation page.


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.