Searching upon multiple date fields in ElasticSearch

Question

I have a document like this:

{
  "listings": {
    "mappings": {
      "listing": {
        "properties": {
          "auctionOn": {
            "type": "date"
          },
          "inspections": {
            "type": "nested",
            "properties": {
              "endsOn": {
                "type": "date"
              },
              "startsOn": {
                "type": "date"
              }
            }
          },
        // more fields.. snipped for brevity
        }
      }
    }
  }
}

and i would like to perform the following search: (needs to be a bool filter.. no scoring req'd)

• return documents any of the inspections.startsOn matches any of the dates provided (if they are provided) OR
• return documents where auctionOn matches the date provided (if it's provided) they can also specify to search for a) inspections only, b) auctions only. if not provided, either of the dates need to match.

So in other words, possible searches:

• Search where there are any inspections/auctions
• Search where there are any inspections
• Search where there are any auctions
• Search where there are any inspections/auctions on the dates provided
• Search where there are any inspections on the dates provided
• Search where there are any auctions on the dates provided

Now, i'm already in a bool query filter:

{
   "query":
   {
      "bool":
      {
          "filter":[{"terms":{"location.suburb":["Camden"]}}
      }
   }
}

and i need this new filter to be seperate. so.. this is like a nested or filter, within a main bool filter?

So if provided "Suburb = Camden, Dates = ['2018-11-01','2018-11-02']'

then it should return documents where the suburb = Camden and either the inspections or auction date includes one of the dates provided.

I'm kinda stumped on how to do it, so any help would be much appreciated!

Answer 1

There will lot of bool query combinations for the cases you mentioned in the question. Taking the example you mention i.e.

So if provided "Suburb = Camden, Dates = ['2018-11-01','2018-11-02']'

then it should return documents where the suburb = Camden and either the inspections or auction date includes one of the dates provided.

Assuming your location filter is working as expected, for dates part in the above e.g. additions to the query will be:

{
  "query": {
    "bool": {
      "filter": [
        {
          "terms": {
            "location.suburb": [
              "Camden"
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "terms": {
                  "auctionOn": [
                    "2018-11-01",
                    "2018-11-02"
                  ]
                }
              },
              {
                "nested": {
                  "path": "inspections",
                  "query": {
                    "bool": {
                      "should": [
                        {
                          "terms": {
                            "inspections.startsOn": [
                              "2018-11-01",
                              "2018-11-02"
                            ]
                          }
                        },
                        {
                          "terms": {
                            "inspections.endsOn": [
                              "2018-11-01",
                              "2018-11-02"
                            ]
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}