Change past dates to today's date while sorting in ElasticSearch

[hrishi_es (Hrishi)]

I am trying to sort Elasticsearch index on a date field in asc order. I have two conditions which I need to take care of while sorting.

1. All past dates should be considered as today's date. This matters since I am sorting on multiple fields.

2. All null values should be at the end.

I went through different suggestions of using function_score and script_score. I am new to Elasticsearch and I am unable to make that work for my usecase

So my query without applying any of above conditions look like this.

POST /index/type/_search
{
"query" : {
"term" : { "Id" : "12345" }
},
"sort" : {
"date" : {"order" : "asc"},
"sales" : {"order" : "desc"}
}
}

[nik9000 (Nik Everett)]

First: wrap code in ``` so it is more readable.

For the "null values should be at the end" thing you probably want the missing support from sorting.

For the date rules maybe you want script based sorting? It sounds like you have some rules about how you combine dates, or at least how you clamp their values. If you use script based scoring you'll have to pass now in as a parameter to the script. We intentionally don't expose that at the moment. Also, I'm not sure how script based scoring combines with the field scoring. That is a thing to experiment with.

[hrishi_es (Hrishi)]

Hi Nik,
Thanks for responding.

what do you mean "We intentionally don't expose that at the moment" ? Does this mean i need to look for workaround it ?

I am looking to implement it with Java and have Elatsicsearch version 5.1 I'll experiment with it today and post my findings on how it works.

[hrishi_es (Hrishi)]

@hrishi_es

POST /index/type/_search{ "query" : { "term" : { "Id" : "12345" } }, "sort" : { "date" : {"order" : "asc"}, "sales" : {"order" : "desc"} }}

Posting here my solution just in case someone else needs it.

POST /inventoryplanningitems/inventoryplanningitem/_search
{
"query" : {
"bool" : {
"must": [{
"match": {
"Id": "522291442"
}
}, {
"match": {
"status": "active"
}
}]
}
},
"sort": {
"_script": {
"type": "string",
"order": "asc",
"script": {
"lang": "painless",
"inline": "def sf = new SimpleDateFormat("yyyy-MM-dd"); def today=sf.parse('2017-04-04'); if(doc['date'].value > today.getTime()) {doc['date'].value} else {if(doc['date'].value == 0) {99999999999L} else {today.getTime()}"
}
},
"sales" : {"order" : "desc"}
}
}