ElasticSearch. Select by date range in array of dates and sort by matching dates

[AHeavyObject (AHO)]

Every document has several dates.
I want to select documents withing a date range (any of dates fits the range - the document matches). This part works.
And I want to sort the selected document by date, but sorting includes all dates, not only the matching ones.

E.g.

doc 1
        "1990-01-01",
        "2000-01-01",
        "2000-01-02",
        "2020-01-01"

doc 2
        "1990-01-01",
        "2000-05-01",
        "2020-01-01"

doc 3
        "1990-01-01",
        "2000-03-01",
        "2020-01-01"
doc 4
        "1990-01-01",
        "2002-03-01",
        "2020-01-01"

If I select range

                  "gte": "1991-03-01",
                  "lte": "2001-03-01"

then doc 1, doc 2, doc 3 matches it.

And if I sort by date I expect to see for asc (dates I expect to take part in sorting)

doc 1 -> 2000-01-01
doc 3 -> 2000-03-01
doc 2 -> 2000-05-01

But it seems to use the earliest date so ordering doesn't affect it.

Here I attache test data I try to make work:

DELETE my-index-000001

PUT my-index-000001
{
  "mappings": {
    "properties": {
      "dtmSent": {
        "type": "nested",
        "properties" : {
            "dtmSent" : {
                "type" : "date"
            }
        }

      }
    }
  }
}
PUT my-index-000001/_doc/1
{
  "dtmSent" : [
    {
      "dtmSent" : [
        "1990-01-01",
        "2000-01-01",
        "2000-01-02",
        "2020-01-01"
      ]
    }
  ]
}
PUT my-index-000001/_doc/2
{
  "dtmSent" : [
    {
      "dtmSent" : [
        "1990-01-01",
        "2000-05-01",
        "2020-01-01"
      ]
    }
  ]
}
PUT my-index-000001/_doc/3
{
  "dtmSent" : [
    {
      "dtmSent" : [
        "1990-01-01",
        "2000-03-01",
        "2020-01-01"
      ]
    }
  ]
}
PUT my-index-000001/_doc/4
{
  "dtmSent" : [
    {
      "dtmSent" : [
        "1990-01-01",
        "2002-03-01",
        "2020-01-01"
      ]
    }
  ]
}

GET /my-index-000001/_search
{
  "query": {
    "nested": {
      "path": "dtmSent",
      "query": {
        "bool": {
          "must": [
            {
              "range": {
                "dtmSent.dtmSent": {
                  "gte": "1991-03-01",
                  "lte": "2001-03-01"
                }
              }
            }
          ]
        }
      }
    }
  },
  "sort": [
    {
      "dtmSent.dtmSent": {
        "order": "asc",
        "nested": {
          "path" : "dtmSent",
          "filter" :{
            "range": {
              "dtmSent.dtmSent": {
                "gte": "1991-03-01",
                "lte": "2001-03-01"
              }
            }
          }
        }
      }
    }
  ]
}

Please advice what I miss.

[AHeavyObject (AHO)]

Here is the solution: https://stackoverflow.com/a/64010705/518704

Your docs are slightly malformed. If you want proper nested-ness, you'll need to refactor them like so:

{
  "dtmSent":[
    {
      "dtmSent":"1990-01-01"
    },
    {
      "dtmSent":"2000-05-01"
    },
    {
      "dtmSent":"2020-01-01"
    }
  ]
}

instead of the array-of-strings.

The index mapping can stay the same, the query too as well as the sorting part. After that, the order will work as you expect 1 -> 3 -> 2.

After you drop the index, here's the _bulk command to get you started (only after setting the mapping of course):

POST _bulk
{"index":{"_index":"my-index-000001","_type":"_doc","_id":1}}
{"dtmSent":[{"dtmSent":"1990-01-01"},{"dtmSent":"2000-01-01"},{"dtmSent":"2000-01-02"},{"dtmSent":"2020-01-01"}]}
{"index":{"_index":"my-index-000001","_type":"_doc","_id":2}}
{"dtmSent":[{"dtmSent":"1990-01-01"},{"dtmSent":"2000-05-01"},{"dtmSent":"2020-01-01"}]}
{"index":{"_index":"my-index-000001","_type":"_doc","_id":3}}
{"dtmSent":[{"dtmSent":"1990-01-01"},{"dtmSent":"2000-03-01"},{"dtmSent":"2020-01-01"}]}
{"index":{"_index":"my-index-000001","_type":"_doc","_id":4}}
{"dtmSent":[{"dtmSent":"1990-01-01"},{"dtmSent":"2002-03-01"},{"dtmSent":"2020-01-01"}]}