elasticsearch nested range query

Question

Suppose i want this structure for a document:

{
  "hours": {
    "open": [
      {
        "start": 10,
        "end": 19
      },
      {
        "start": 21,
        "end": 29
      }
      ...
    ],
    "closed": [
      {
        "start": 100,
        "end": 199
      },
      {
        "start": 201,
        "end": 299
      }
      ...
    ]
  }
}

whose index has this mapping:

{
  "mappings": {
    "_doc": {
      "properties": {
        "hours": {
          "properties": {
            "open": {
              "type": "nested",
              "properties": {
                "start": { "type": "integer" },
                "end": { "type": "integer" }
              }
            },
            "closed": {
              "type": "nested",
              "properties": {
                "start": { "type": "integer" },
                "end": { "type": "integer" }
              }
            }
          }
        }
      }
    }
  }
}

In the Elasticsearch Query DSL, how do i find all documents where 20 lies inside an open segment and not inside a closed segment. The query I tried was incorrect.

failed query

{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "must": [
              {
                "nested": {
                  "path": "hours.open",
                  "query": {
                    "range": {
                      "hours.open.start": { "lte": 20 }
                    }
                  }
                }
              },
              {
                "nested": {
                  "path": "hours.open",
                  "query": {
                    "range": {
                      "hours.open.end": { "gte": 20 }
                    }
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "bool": {
                  "must": [
                    {
                      "nested": {
                        "path": "hours.closed",
                        "query": {
                          "range": {
                            "hours.closed.start": { "lte": 20 }
                          }
                        }
                      }
                    },
                    {
                      "nested": {
                        "path": "hours.closed",
                        "query": {
                          "range": {
                            "hours.closed.end": { "gte": 20 }
                          }
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

whats wrong with my query? it is returning this document which is not what i intended. 20 does not lie inside an open segment.

Answer 1

I finally got it working. The following is the correct query:

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "hours.open",
            "query": {
              "bool": {
                "must": [
                  { "range": { "hours.open.start": { "lte": 20 } } },
                  { "range": { "hours.open.end": { "gte": 20 } } }
                ]
              }
            }
          }
        }
      ],
      "must_not": [
        {
          "nested": {
            "path": "hours.closed",
            "query": {
              "bool": {
                "must": [
                  { "range": { "hours.closed.start": { "lte": 20 } } },
                  { "range": { "hours.closed.end": { "gte": 20 } } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

With that said, it looks like my original attempt was wrong because there were two different hours.open nested path queries and likewise two different hours.closed nested path queries. The parser must only take one of them for a single path.

Answer 2

Seems like you need to swap lte and gte:

"hours.open.start": { "gte": 20 }
"hours.open.end": { "lte": 20 }

and same for the closing times:

"hours.closed.start": { "gte": 20 }
"hours.closed.end": { "lte": 20 }

Edit: The must and must_not need to be part of the same bool query:

{
    "query": {
        "bool": {
            "must": [{
                    "nested": {
                        "path": "hours.open",
                        "query": {
                            "range": {
                                "hours.open.start": {
                                    "gte": 20
                                }
                            }
                        }
                    }
                },
                {
                    "nested": {
                        "path": "hours.open",
                        "query": {
                            "range": {
                                "hours.open.end": {
                                    "lte": 20
                                }
                            }
                        }
                    }
                }
            ],
            "must_not": [{
                "bool": {
                    "must": [{
                            "nested": {
                                "path": "hours.closed",
                                "query": {
                                    "range": {
                                        "hours.closed.start": {
                                            "gte": 20
                                        }
                                    }
                                }
                            }
                        },
                        {
                            "nested": {
                                "path": "hours.closed",
                                "query": {
                                    "range": {
                                        "hours.closed.end": {
                                            "lte": 20
                                        }
                                    }
                                }
                            }
                        }
                    ]
                }
            }]
        }
    }
}