Elasticsearch's search_after doesn't work with date

user_4452347 0
Viewed 2758

I have an Elasticsearch query that looks something like this,

{
  size: 25,
  query: {
    bool: {
      filter: ...,
      must: ...
    }
  },
  sort: [
    {
      created_at: {
        order: "desc
      }
    },
    {
      id: {
        order: "desc"
      }
    }
  ],
  from: 0,
  search_after: ["2018-10-25T18:04:13.488Z", "8"]
}

When I run this I get an error saying,

"reason": Failed to parse search_after value for field [created_at], caused_by":{"type":"number_format_exception","reason":"For input string: \"2018-10-23T21:41:03.167Z\"

I tried mapping the created_at property with type of date and format of date_time, but that didn't seem to help.

Can anyone shed some light on how I can solve this?

elasticsearch
3 Answers

You need to pass the date as the number of milliseconds since epoch, i.e. instead of 2018-10-25T18:04:13.488Z try passing 1540491254488

{
  size: 25,
  query: {
    bool: {
      filter: ...,
      must: ...
    }
  },
  sort: [
    {
      created_at: {
        order: "desc
      }
    },
    {
      id: {
        order: "desc"
      }
    }
  ],
  from: 0,
  search_after: [1540491254488, "8"]
                       ^
                       |
                  change this

Note that you can continue indexing your documents as you do now, but when creating your query, you need to feed that number into search_after.

user_46045790

Additionally, the Elasticsearch documentation is misleading on this, as their example looks like:

    "search_after": [1463538857, "654323"],

which leads you to believe that it is seconds, rather than milliseconds, since the epoch.

user_338970

When sorting on a date field, Elastic Search will always return sort value in epoch_millis, even if you have defined a different format in your mapping.

It's explained in the docs here:

"Note that the sort values that are returned are all in milliseconds-since-the-epoch."

So if you have defined a different format and rely on the sort to implement pagination, you need to convert from epoch_millis to your desired format. Probably the easiest way to fix this is to include epoch_millis in your date mappings format, ie:

  "mappings": {
    "properties": {
      "date": {
        "type":   "date",
        "format": "strict_date_optional_time||epoch_millis"
      }
    }
  }

Reference docs

user_16805350
Related