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
– Sunil Yerra, Commented Jan 20, 2021 at 11:44
so you want to fetch those documents that have either plannedCompleteDate OR dueDate fields, but not both these fields? — Bhavya
– Bhavya, Commented Jan 20, 2021 at 11:48
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
– Sunil Yerra, Commented Jan 20, 2021 at 11:51
are there any documents in your index that contain both these fields ? — Bhavya
– Bhavya, Commented Jan 20, 2021 at 11:52
No I guess, to be sure, how can I check if there are any such documents? — Sunil Yerra
– Sunil Yerra, Commented Jan 20, 2021 at 11:57

Bhavya · Accepted Answer · 2021-01-20 12:02:09Z

1

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"
        }
      }
    ]

answered Jan 20, 2021 at 12:02

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bhavya Over a year ago

@Sunil Yerra please go through the answer, and let me know if this was the issue ?

Sunil Yerra Over a year ago

Thanks for the quick help, i ll try this and let you know my experience.

Bhavya Over a year ago

Sure @Sunil Yerra, looking forward to get feedback from you :)

Sunil Yerra Over a year ago

Thanks for the help, it worked. I m now able to enhance that query for more useful results. Thanks brother.

Sunil Yerra Over a year ago

My range should be less than now, in the example i gave some dates for reference.... Below is the query which worked for me { "bool": { "should": [ { "range": { "dueDate": { "lt": "now" } } }, { "range": { "plannedCompleteDate": { "lt": "now" } } } ] } }

Collectives™ on Stack Overflow

conditionally query for fields in elasticsearch

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related