CODEWITHSUNDEEP
sortingdateelasticsearchscriptingelasticsearch-painless
Jordan Ell
Jordan Ell

Reputation: 1805

Elasticsearch sorting buckets of dates with script

I have an interesting sorting problem with Elasticsearch. I have the following data:

Item =>     A     B      C         D    E    F
Time =>    -3    -2     -1    0    1    2    3

The above graphic shows that I have 6 pieces of data, items A-F, and they each occur at a certain time between -3 and 3, where the current time is t0.

I am looking to creating a sort in Elasticsearch which sorts the items in the following order:

DEFCBA

That is to say, any items happening in the future are placed first in the results, and sorted in ascending order, any items happening in the past are place second in the results, and sorted in descending order.

So far, I have the following sort:

sort: [
  {
    _script: {
      type: 'number',
      script: {
        lang: 'painless',
        inline: "if (doc['item_date'].value - params.current_time > 0) {return 1;} else {return 0}",
        params: {
          current_time,
        },
      },
      order: 'desc',
    },
  },
  { item_date: { order: 'desc' } },
]

The above do place the future items first, the past items second, but it does not order the future items in ascending order (it sorts the past items, and all items for that matter in descending order).

I am not sure what script I can write to sort the future items separately from the past items based on the outcome of the first script.

Any help would be much appreciated.

Upvotes: 1

Views: 734

Answers (1)

kradical
kradical

Reputation: 44

I believe you are close, but have to return some value instead of 1 for the "future" dates.

Try substituting 1 for 1.0/doc['start_date'].value so it is inversely proportional to date (larger date values are further in the future).

Full example as above:

sort: [
  {
    _script: {
      type: 'number',
      script: {
        lang: 'painless',
        inline: "if (doc['item_date'].value > params.current_time) {return 1.0/doc['start_date'].value;} else {return 0}",
        params: {
          current_time,
        },
      },
      order: 'desc',
    },
  },
  { item_date: { order: 'desc' } },
]

Upvotes: 2

Related Questions