Elasticsearch嵌套范围查询问题：筛选20在开放时段且不在关闭时段的文档

The core issue with your query is that you're checking conditions across different nested objects in both the open and closed arrays, rather than ensuring both conditions apply to the same nested object. Here's a breakdown:

Problem 1: The open Interval Check #

Your current query uses two separate nested clauses for hours.open:

• One checks if any open interval has a start ≤ 20
• Another checks if any open interval has an end ≥ 20

These could match two different entries in the open array. For example, if you have open: [{start:5, end:15}, {start:25, end:30}], your query would incorrectly return this document—even though 20 isn't in any open interval—because one entry starts before 20 and another ends after 20.

Problem 2: The closed Interval Check #

Similarly, your must_not clause for closed uses two separate nested queries. This means it would exclude documents where any closed interval starts ≤20 and any closed interval ends ≥20—even if those are two different intervals that don't actually cover 20. For example, closed: [{start:5, end:15}, {start:25, end:30}] would trigger the must_not, incorrectly excluding a document where 20 isn't in any closed interval.

The Corrected Query #

To fix this, you need to combine the range conditions into single nested queries for both open and closed, ensuring both conditions apply to the same nested object:

{
  "query": {
    "bool": {
      "must": [
        // Check that at least one open interval includes 20
        {
          "nested": {
            "path": "hours.open",
            "query": {
              "bool": {
                "must": [
                  { "range": { "hours.open.start": { "lte": 20 } } },
                  { "range": { "hours.open.end": { "gte": 20 } } }
                ]
              }
            }
          }
        }
      ],
      "must_not": [
        // Ensure no closed interval includes 20
        {
          "nested": {
            "path": "hours.closed",
            "query": {
              "bool": {
                "must": [
                  { "range": { "hours.closed.start": { "lte": 20 } } },
                  { "range": { "hours.closed.end": { "gte": 20 } } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

How This Works: #

1. Open Interval Check: The single nested query on hours.open ensures there's at least one entry where both start ≤20 and end ≥20—meaning 20 falls within that specific open interval.
2. Closed Interval Check: The single nested query inside must_not ensures there are no entries in hours.closed where both start ≤20 and end ≥20—so 20 isn't in any closed interval.

This way, you're correctly filtering for documents where 20 is in an open window and not in any closed window.

内容的提问来源于stack exchange，提问作者Vincent