CODEWITHSUNDEEP
javaelasticsearch
Hamid Ghasemi
Hamid Ghasemi

Reputation: 892

elasticsearch query on comparing 2 fields (using java)

I've an index in my elasticsearch and I want to have a query to compare 2 date fields. assuming fields name are creationDate and modifiedDate. I want to get all documents which these 2 dates are the same in them.

I know it was possible to use FilteredQuery which is deprecated right now. something like the bellowing code:

FilteredQueryBuilder query = QueryBuilders.filteredQuery(null,
    FilterBuilders.scriptFilter("doc['creationDate'].value = doc['modifiedDate'].value"));

Also it's maybe possible to write manual scripts as string, but I doubt that this is the right solution. Any idea's to create the properly query would be appreciated.

Upvotes: 0

Views: 842

Answers (1)

Val
Val

Reputation: 217274

Filtered query have been replaced by bool/filter queries You can do it like this:

BoolQueryBuilder bqb = QueryBuilders.boolQuery()
    filter(QueryBuilders.scriptQuery("doc['creationDate'].value = doc['modifiedDate'].value"));

However, instead of using scripts at search time, you'd be better off creating a new field at indexing time that contains the information of whether creationDate and modifiedDate are the same dates. Then, you could simply check that flag at query time, it would be much more optimal and fast.

If you don't want to reindex all your data, you can update all of them with that flag, simply run an update by query like this:

POST my-index/_update_by_query

{
  "script": {
    "source": """
    def creationDate = Instant.parse(ctx._source.creationDate);
    def modifiedDate = Instant.parse(ctx._source.modifiedDate);
    ctx._source.modified = ChronoUnit.MICROS.between(creationDate, modifiedDate) > 0;
    """,
    "lang": "painless"
  },
  "query": {
    "match_all": {}
  }
}

And then your query will simply be

BoolQueryBuilder bqb = QueryBuilders.boolQuery()
    filter(QueryBuilders.termQuery("modified", "false");

Upvotes: 2

Related Questions