Elasticsearch should with must_not - non-nested

Question

New to Elasticsearch and working on a legacy model that I'm hesitant to change. I have date field that we won't input if it the value is null (I think because of the way ES handles 0000-00-00). I want to be able to query the data that either has a specific date OR where the field doesn't exist. Here is what I have:

{
    "size": 10000,
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "AccountID": "xxxx"
                    }
                },
                {
                    "term": {
                        "LocationID": "xxxx"
                    }
                },
                {
                    "should": [
                        {
                            "range": {
                                "CloseDate": {
                                    "gte": "2020-11-01",
                                    "lte": "2020-12-02"
                                }
                            }
                        },
                        {
                            "bool": {
                                "must_not": {
                                    "exists": {
                                        "field": "CloseDate"
                                    }
                                }
                            }
                        }
                    ]
                }
            ]
        }
    }
}

I get and error of [should] query malformed, no start_object after query name when trying this though. Is there an alternative syntax or is something really malformed?

Answer 1

A must clause can have multiple queries within []. Note that should is also a clause of bool query and not a query in itself. Therefore it should be inside bool.

{
  "size": 10000,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "AccountID": "xxxx"
          }
        },
        {
          "term": {
            "LocationID": "xxxx"
          }
        },
        {
          "bool": {            <------------- Note this
            "should": [
              {
                "range": {
                  "CloseDate": {
                    "gte": "2020-11-01",
                    "lte": "2020-12-02"
                  }
                }
              },
              {
                "bool": {
                  "must_not": {
                    "exists": {
                      "field": "CloseDate"
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Answer 2

The errror clearly indicates that your query is not correctly formed. You are missing a ] bracket. Try out this query:

{
  "size": 10000,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "AccountID": "xxxx"
          }
        },
        {
          "term": {
            "LocationID": "xxxx"
          }
        }
      ],                                   <-- note this
      "should": [
        {
          "range": {
            "CloseDate": {
              "gte": "2020-11-01",
              "lte": "2020-12-02"
            }
          }
        },
        {
          "bool": {
            "must_not": {
              "exists": {
                "field": "CloseDate"
              }
            }
          }
        }
      ]
    }
  }
}