need something like coalesce in elasticsearch

Question

My current elasticsearch query is-

{

            "must": [
                {
                    "range": {
                        "firstClosedAt": {
                            "gte": 1667948400000,
                            "lte": 1668034800000
                        }
                    }
                },

                {
                    "term": {
                        "status": "CLOSED"

                }

}

I want to modify it such that if "firstClosedAt" is null or not present then look for "closedAt". Just like we have coalesce("firstClosedAt","closedAt") in sql

Help would be appreciated

Answer 1

There's no coalesce equivalent in ES, but you can do the query like below, which can read like: "either use firstClosedAt OR use closedAt if firstClosedAt does not exist":

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "status": "CLOSED"
          }
        },
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "range": {
                  "firstClosedAt": {
                    "gte": 1667948400000,
                    "lte": 1668034800000
                  }
                }
              },
              {
                "bool": {
                  "must_not": {
                    "exists": {
                      "field": "firstClosedAt"
                    }
                  },
                  "filter": {
                    "range": {
                      "closedAt": {
                        "gte": 1667948400000,
                        "lte": 1668034800000
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

You could, however, create a much simpler query if you create another date field at indexing time which would either take the value of firstClosedAt or closedAt if firstClosedAt does not exist