CODEWITHSUNDEEP
elasticsearchaws-elasticsearch
kaz
kaz

Reputation: 3

ElasticSearch: Querying if today is between and series of start and end dates in list

If I have some data with a field with multiple sets of start/end dates.. for example:

{
     id: 1,
     title: "My Title",
     availability[{start: 01-01-2020, end: 01-05-2020},{start: 02-01-2020, end: 02-22-2020}]
}

Is it possible in elasticsearch to build a query to check if today (or any given date) falls within any of the start/end date combinations in the list?

Or would I need to structure my data differently to make this work?

Previously, I was dealing with just one start and one end date and could store them as their own fields and do a gte, lte combination to check.

Update:

if I add them as nested fields. e.g.:

"avails" : {
    "type" : "nested",
    "properties" : {
        "availStart" : { "type" : "date" },
        "availEnd" : { "type" : "date" }
    }
}

If I do my search like this:

{
    "query": {
        "nested" : {
            "path" : "avails",
            "query" : {
                "term" : {
                    { "range" : {"avails.start" : {"lte": "now"}}},
                    { "range" : {"avails.end" : {"gt" : "now"}}}
                }
            }
        }
    }
}

will it evaluate this for each nested record and return any parent record with a child record that matches?

Upvotes: 0

Views: 512

Answers (1)

Joe - Check out my books
Joe - Check out my books

Reputation: 16933

It's good that you've chosen nested fields. Now you just need to make sure the mappings, field names, and the query are all consistent.

  1. The date mapping including the format:
PUT myindex
{
  "mappings": {
    "properties": {
      "avails": {
        "type": "nested",
        "properties": {
          "start": { "type": "date", "format": "MM-dd-yyyy" },
          "end": { "type": "date", "format": "MM-dd-yyyy" }
        }
      }
    }
  }
}
  1. Syncing your doc
POST myindex/_doc
{
  "id": 1,
  "title": "My Title",
  "avails": [
    {
      "start":"01-01-2020",
      "end": "01-05-2020"
    },
    {
      "start": "02-01-2020",
      "end": "02-22-2020"
    }
  ]
}
  1. And finally the query. Yours was malformed -- if you want a logical AND, you'll need to wrap the range queries in a bool + must:
POST myindex/_search
{
  "query": {
    "nested": {
      "path": "avails",
      "query": {
        "bool": {
          "must": [
            { "range" : {"avails.start" : {"lte": "now"}}},
            { "range" : {"avails.end" : {"gt" : "02-01-2020"}}}
          ]
        }
      }
    }
  }
}

Upvotes: 0

Related Questions