Nested multiple date-range intervals

Elastic Stack Elasticsearch
1

I have an index, that will hold a 'job' and each job will have a couple of date-intervals where that job can be performed. In our case it a plumbing job :slight_smile:

These date-intervals are limited, so they do not expand until forever. At most 5 date-intervals per job.

PUT /testnesteddateintervals/
 {
 	"mappings":{
 		"properties": {
 			"JobName": {
 				"type": "text"
 			},
 			"dintervals": {
 				"type": "nested",
 				"properties":{					
 					"time_frame": {
 						"type": "date_range", 
 						"format": "yyyy-MM-dd"
 					}
 				} 				
 			}
 		}
 	}
 }

PUT /testnesteddateintervals/_doc/1
{
	"JobName": "PlumbingWork",
	"dintervals": [
		{
			 "gte" : "2022-03-10", 
    	                 "lte" : "2022-03-30"
		},
		{
			 "gte" : "2022-04-10", 
    	                  "lte" : "2022-04-30"
		}
	]	
}

How can I take a bunch of 'intervals' where the 'plumber' has time, and get if he can fullfil the required dintervals.

For example suppose the plumbers schedule is like this.


{
	"PlumberName": "Plumber Joe",
	"availableIntervals": [
		{
			 "gte" : "2022-03-12", 
    	 "lte" : "2022-03-14"
		},
		{
			 "gte" : "2022-02-10", 
    	 "lte" : "2022-03-11"
		}
	]	
}

How can I do a query to return a true or false if the schedule is fullfiled.

2

This is the solution i Found, multiple bools in a should.

Get /testnesteddateintervals/_search

{
   "query":{
      "bool":{
         "must":[
            {
               "nested":{
                  "path":"dintervals",
                  "query":{
                     "bool":{
                        "should":[
                           {
                              "bool":{
                                 "must":[
                                    {
                                       "range":{
                                          "dintervals.gte":{
                                             "lte":"2022-03-31"
                                          }
                                       }
                                    },
                                    {
                                       "range":{
                                          "dintervals.lte":{
                                             "gte":"2022-03-31"
                                          }
                                       }
                                    }
                                 ]
                              }
                           },
													 {
                              "bool":{
                                 "must":[
                                    {
                                       "range":{
                                          "dintervals.gte":{
                                             "lte":"2022-03-30"
                                          }
                                       }
                                    },
                                    {
                                       "range":{
                                          "dintervals.lte":{
                                             "gte":"2022-03-30"
                                          }
                                       }
                                    }
                                 ]
                              }
                           }
                        ]
                     }
                  }
               }
            }
         ]
      }
   }
}
1 Like
3

Take a look at the range field type documentation - the tiny, but important point is the relation parameter to find a hole or an overlapping time to book an appointment.

Hope that helps!

1 Like
4

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