Date Range for Array fields

Elastic Stack Elasticsearch
1

I am looking for how to filter date range from an array.
Sample Data looks like below

"airings":[{
    "startTime":"2015-08-29T10:00:00+0000",
    "endTime":"2015-08-30T21:30:00+0000"
  },{
    "startTime":"2015-08-29T10:00:00+0000",
    "endTime":"2015-08-31T21:30:00+0000"
  }]

Need a date range filter that search airings between a given startTime and endTime.

{
  "query": {
    "bool": {
      "must": [
        {
          "range": 
            {
                "airings.startTime": {
                  "gte": "2015-08-30T12:00:00.000Z"
                },
                "airings.endTime": {
                  "lte": "2015-08-30T01:00:00.000Z"
                }
            }
        }
     ]
    }
  },
  "from": 0,
  "size": 128
}

Can anyone help to see if this date range feature is available on an array or not? Any other alternative for this kind of search? Appreciate your help.

2

Hi Kathy,

You may just have a typo, unless I'm misunderstanding what you're trying to do. With the example you gave, the airings both have a startTime of 2015-08-29 (at 10:00:00) and the query has startTime to be greater than or equal to 2015-08-30 (at 12:00:00). In the query, if you change startTime to be gte a time before a given startTime (2015-08-29 at 00:00:00, for example), the query works as expected:

{
  "query": {
    "bool": {
      "must": [
        {
          "range": 
            {
                "airings.startTime": {
                  "gte": "2015-08-29T01:00:00.000Z"
                },
                "airings.endTime": {
                  "lte": "2015-08-31T12:00:00.000Z"
                }
            }
        }
     ]
    }
  },
  "from": 0,
  "size": 128
}
3

In addition to the problem pointed out by Shane, your query will match match a document with a start time after the beginning of one airing and an end time before the end of a different airing. You could work around this by using nested objects and nested queries:
https://www.elastic.co/guide/en/elasticsearch/guide/current/nested-objects.html

Or you could denormalize and store a separate copy of the document for each airing (might give faster query performance this way).

  • David
4

Thank you Shane and David.
I am still not successful, my search result is returning data that is not in the requested range. Not sure, if my query is having issues. I have pasted here my query. and the result that i am getting.
Query:

  {
      "query": {
        "bool": {
          "must": [
            {
              "bool": {
                "should": [
                  {
                    "match_phrase": {
                      "title": "ABC"
                    }
                  }
                ]
              }
            },
            {
              "range": {
                "airings.startTime": {
                  "gte": "2015-08-30T23:03:06.283Z"
                }
              }
            },
            {
              "range": {
                "airings.endTime": {
                  "lte": "2015-08-31T23:03:06.283Z"
                }
              }
            }
          ]
        }
      },
      "from": 0,
      "size": 128
    }

And my result for search has records that are out of date range.

"assetInfo":{
    "title":["ABC"],
}
"airings":[{
    "startTime":"2015-08-05T18:30:00+0000",
    "endTime":"2015-08-05T19:00:00+0000"
  },{
    "startTime":"2015-09-02T20:00:00+0000",
    "endTime":"2015-09-02T20:30:00+0000"
  }]

Can you see if the title is making any difference here.
In this case, i requested for dates that match 8/30 and 8/31. Where as i got 8/5 and 9/2 dates.
Thanks a lot for your help

5

I believe you're running into the nested object issue that David was suggesting. By default, elasticsearch will flatten the data you send it. So your "airings" object won't maintain it's structure unless you explicitly tell elasticsearch that it's a nested object. So without setting up airings as type nested, it will treat your data as:

{
  "airings":{
    "startTime":[2015-08-05, 2015-09-02],
    "endTime":  [2015-08-05, 2015-09-02]
  }
}

And when you query, startTime >= 2015-08-30 and endTime <= 2015-08-31, elasticsearch sees that the second element of the startTime array matches while the first element of the endTime array matches. Mapping airings as a nested type will maintain the structure. Alternatively, as David says, you could denormalize your data and store each entry as a airing document. There's a great blog post about managing object relationships like these which may help as well!

6

I think, as shown in the example of https://www.elastic.co/guide/en/elasticsearch/guide/current/nested-objects.html, i am doing nesting. Isn't it the nesting? i am confused

{
      "query": {
        "bool": {
          "must": [
            { "match": { "name": "Alice" }}, //{ "range": {"airings.startTime":{"gte": "2015-08-30T23:03:06.283Z"}}},
            { "match": { "age":  28      }} // {"range":{airings.endTime":{"lte": "2015-08-31T23:03:06.283Z"}}}
          ]
        }
      }
    }

For my example data, does the denormalization means like this?

"assetInfo":{
    "title":["ABC"],
}
"airings_0_startTime":"2015-08-05T18:30:00+0000",
"airings_0_endTime":"2015-08-05T19:00:00+0000"
"airings_1_startTime":"2015-09-02T20:00:00+0000",
"airings_1_endTime":"2015-09-02T20:30:00+0000"
  }]

Then how to do the range filter. Sorry for many questions. I am new to elastic search.