Elasticsearch query: range query on two fields, but one is optional field

Question

I need to search my index based on a timestamp.

The documents have these field combinations:

• start_time and end_time

or

• just start_time (no end_time field)

Pseudo query: . For a given timestamp, I wish to return all documents where an id matches, and also:
timestamp >= start_time && timestamp < end_time

but if there is no end_time field, then the query needs to be this:
(not exists end_time) && (timestamp > start_time)

Elastic query . This is where I am going mad. I can't get an elastic query equivilent to that pseudo query above. Perhaps I am approaching it the wrong way (entirely possible). Here is what I have:

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "id_s": "SomeIdValue"
          }
        },
        {
          "bool": {
            "should": [
              {
                "must": [
                  {
                    "must_not": [
                      {
                        "exists": {
                          "field": "end_time_dt"
                        }
                      }
                    ]
                  },
                  {
                    "range": {
                      "start_time_dt": {
                        "lte": "2019-07-12T03:20:22"
                      }
                    }
                  }
                ]
              },
              {
                "filter": [
                  {
                    "range": {
                      "start_time_dt": {
                        "lte": "2019-07-12T03:20:22"
                      }
                    }
                  },
                  {
                    "range": {
                      "end_time_dt": {
                        "gte": "2019-07-12T03:20:22"
                      }
                    }
                  }
                ]
              }
            ]
          }
        }
      ]
    }
  }
}

But this gives me [must] query malformed, no start_object after query name

How do I construct this query? Am I on the right track?

thanks in advance!

Answer 1

Your query is syntactically wrong. The correct query would be:

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "id_s": "SomeIdValue"
          }
        },
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "end_time_dt"
                      }
                    }
                  ],
                  "must": [
                    {
                      "range": {
                        "start_time_dt": {
                          "lte": "2019-07-12T03:20:22"
                        }
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "range": {
                        "start_time_dt": {
                          "lte": "2019-07-12T03:20:22"
                        }
                      }
                    },
                    {
                      "range": {
                        "end_time_dt": {
                          "gte": "2019-07-12T03:20:22"
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Answer 2

There is a slight mistake in the logic. Ideally, comparison should be like this gte start_time_dt and lte end_time_dt. You did other way round so that translates to timestamp <= start_time && timestamp > end_time.

The correct query is

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "id_s": "SomeIdValue"
          }
        },
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "end_time_dt"
                      }
                    }
                  ],
                  "must": [
                    {
                      "range": {
                        "start_time_dt": {
                          "gte": "2019-07-12T03:20:22"
                        }
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "range": {
                        "start_time_dt": {
                          "gte": "2019-07-12T03:20:22"
                        }
                      }
                    },
                    {
                      "range": {
                        "end_time_dt": {
                          "lte": "2019-07-12T03:20:22"
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Hope this helps!!

Answer 3

I believe this block should be must not should as mentioned in the answer. The reason I say is both those conditions: must ( not exists AND range ) is what the OP intention I believe

{
          "bool": {
            "must": [     <====== mentioned it as should
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "end_time_dt"
                      }
                    }
                  ],
                  "must": [
                    {
                      "range": {
                        "start_time_dt": {
                          "lte": "2019-07-12T03:20:22"
                        }
                      }
                    }
                  ]
                }
              },