Sort by date + show past results after upcoming results

Question

In Elastic I'd like to sort results by start_date ascending, but with past dates showing up after upcoming dates.

Example desired results:

[
    {id: 5, start_date: '3000-01-01'},
    {id: 7, start_date: '3001-01-01'},
    {id: 8, start_date: '3002-01-01'},
    {id: 1, start_date: '1990-01-01'},
    {id: 4, start_date: '1991-01-01'},
    {id: 3, start_date: '1992-01-01'},
]

Something like this would be possible in SQL:

ORDER BY (start_date > NOW()) DESC, start_date ASC

But I'm not sure how to accomplish this in Elastic. The only thing I can think of would be to set a boolean is_upcoming flag and reindex that every day.

Also I could be limiting and paginating the # of search results, so fetching them in reverse start_date order and then manipulating the results in my code isn't really doable.

Answer 1

It's perfectly possible using a sort script if your start_date is of type date and its format is yyyy-MM-dd (I found YYYY-... to not work properly).

GET future/_search
{
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": "return doc['start_date'].value.millis > params.now ? (doc['start_date'].value.millis - params.now) : Long.MAX_VALUE",
          "params": {
            "now": 1594637988236
          }
        },
        "order": "asc"
      }
    },
    {
      "start_date": {
        "order": "asc"
      }
    }
  ]
}

The parametrized now is needed for synchronization reasons as described here.