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

**URL:** <https://discuss.elastic.co/t/elasticsearch-select-by-date-range-in-array-of-dates-and-sort-by-matching-dates/249509>\
**Category:** Elasticsearch\
**Created:** [September 22, 2020, 12:00pm UTC](https://discuss.elastic.co/t/elasticsearch-select-by-date-range-in-array-of-dates-and-sort-by-matching-dates/249509 "2020-09-22T12:00:47Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![AHeavyObject](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aheavyobject/32/68052_2.png) [@AHeavyObject](https://discuss.elastic.co/u/AHeavyObject)\
**Post date:** [September 22, 2020, 12:00pm UTC](https://discuss.elastic.co/t/elasticsearch-select-by-date-range-in-array-of-dates-and-sort-by-matching-dates/249509/1 "2020-09-22T12:00:48Z")

</div>

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.

```auto
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

```auto
                  "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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![AHeavyObject](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aheavyobject/32/68052_2.png) [@AHeavyObject](https://discuss.elastic.co/u/AHeavyObject)\
**Post date:** [September 22, 2020, 1:38pm UTC](https://discuss.elastic.co/t/elasticsearch-select-by-date-range-in-array-of-dates-and-sort-by-matching-dates/249509/2 "2020-09-22T13:38:36Z")

</div>

Here is the solution: [https://stackoverflow.com/a/64010705/518704](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:

```auto
{
  "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):

```auto
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"}]}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 20, 2020, 1:38pm UTC](https://discuss.elastic.co/t/elasticsearch-select-by-date-range-in-array-of-dates-and-sort-by-matching-dates/249509/3 "2020-10-20T13:38:49Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.