conditionally query for fields in elasticsearch

Question

I m new to Elasticsearch and before posting this question I have googled for help but not understanding how to write the query which i wanted to write.

My problem is I have few bunch of documents which i want to query, few of those documents has field "DueDate" and few of those has "PlannedCompletionDate" but not both exist in a single document. So I want to write a query which should conditionally query for a field from documents and return all documents.

For example below I m proving sample documents of each type and my query should return results from both the documents, I need to write query which should check for field existence and return the document

"_source": { ... "plannedCompleteDate": "2019-06-30T00:00:00.000Z", ... }

"_source": { ... "dueDate": "2019-07-26T07:00:00.000Z", ... }

Forgot to mention, in all documents I m interested to fetch all the documents which has either field dueDate or plannedCOmpletionDate. None of these documents will have both the fields dueDate and plannedCOmpletionDate. — Sunil Yerra
so you want to fetch those documents that have either plannedCompleteDate OR dueDate fields, but not both these fields? — ESCoder
Yes, and none of these documents will contain both the fields. So my query should be something like "select date from document if it contains dueDate or plannedCompletionDate and date should be between '2020-01-01 and '2020-12-31'' " — Sunil Yerra
are there any documents in your index that contain both these fields ? — ESCoder
No I guess, to be sure, how can I check if there are any such documents? — Sunil Yerra

ESCoder ESCoder · Accepted Answer · 2021-01-20T12:02:09

You can use range query with the combination of the boolean query to achieve your use case.

Adding a working example with index mapping, data, search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "plannedCompleteDate": {
        "type": "date",
        "format": "yyyy-MM-dd"
      },
      "dueDate": {
        "type": "date",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

Index Data:

{
  "plannedCompleteDate": "2019-05-30"
}
{
  "plannedCompleteDate": "2020-06-30"
}
{
  "dueDate": "2020-05-30"
}

Search Query:

{
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "plannedCompleteDate": {
              "gte": "2020-01-01",
              "lte": "2020-12-31"
            }
          }
        },
        {
          "range": {
            "dueDate": {
              "gte": "2020-01-01",
              "lte": "2020-12-31"
            }
          }
        }
      ]
    }
  }
}

Search Result:

"hits": [
      {
        "_index": "65808850",
        "_type": "_doc",
        "_id": "1",
        "_score": 1.0,
        "_source": {
          "plannedCompleteDate": "2020-06-30"
        }
      },
      {
        "_index": "65808850",
        "_type": "_doc",
        "_id": "2",
        "_score": 1.0,
        "_source": {
          "dueDate": "2020-05-30"
        }
      }
    ]

conditionally query for fields in elasticsearch

1 Answers