need something like coalesce in elasticsearch-CodePudding

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

CodePudding user response：

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