CODEWITHSUNDEEP
elasticsearch
user1708054
user1708054

Reputation: 191

conditional Query elastic search using java client not working

I am trying to write a fetch query and delete document using elastic search API, which should have below two conditions

  1. if accountType is not "Prepaid" OR
  2. if accountType is not "Postpaid" AND ProductName is not "SIM" AND
  3. timestamp is less than 30 days

I have tried with the below code but it does not fetch the right documents. intent is to fetch all these document and delete from Elastic search index

QueryBuilder query = QueryBuilders.boolQuery()
                .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.matchQuery("ACCOUNT_TYPE", "PREPAID")))
                .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.matchPhraseQuery("ACCOUNT_TYPE","POSTPAID"))
                .mustNot(QueryBuilders.matchPhraseQuery("PRODUCT_NAME", "SIM"))).minimumShouldMatch(1)
                .must(QueryBuilders.rangeQuery(LASTUPDATED_TIMESTAMP_PARAM).lt(interval.getDelDateString()));

approch 2: I have also tried to run the above generate query in Kibana dev tools and still it doesnt give right results.

please suggest me where i am going wrong with the above code to satisfy the condition of fetching the data.

Query formed is something like below post @Val resposne

{"bool":{"filter":[{"range":{"lastupdatedTimestamp":{"from":null,"to":"2022-03-12T22:00:00","include_lower":true,"include_upper":false,"boost":1}}}],"should":[{"bool":{"must_not":[{"match":{"ACCOUNT_TYPE":{"query":"PREPAID","operator":"OR","prefix_length":0,"max_expansions":50,"fuzzy_transpositions":true,"lenient":false,"zero_terms_query":"NONE","auto_generate_synonyms_phrase_query":true,"boost":1}}}],"adjust_pure_negative":true,"boost":1}},{"bool":{"must_not":[{"match":{"ACCOUNT_TYPE":{"query":"POSTPAID","operator":"OR","prefix_length":0,"max_expansions":50,"fuzzy_transpositions":true,"lenient":false,"zero_terms_query":"NONE","auto_generate_synonyms_phrase_query":true,"boost":1}}},{"match":{"PRODUCT_NAME":{"query":"SIM","operator":"OR","prefix_length":0,"max_expansions":50,"fuzzy_transpositions":true,"lenient":false,"zero_terms_query":"NONE","auto_generate_synonyms_phrase_query":true,"boost":1}}}],"adjust_pure_negative":true,"boost":1}}],"adjust_pure_negative":true,"minimum_should_match":"1","boost":1}}

Upvotes: 0

Views: 813

Answers (2)

user1708054
user1708054

Reputation: 191

here is the elastic search java api call to do a conditional fetch of records and delete it later

QueryBuilder query = QueryBuilders.boolQuery()
         .filter(QueryBuilders.rangeQuery(LASTUPDATED_TIMESTAMP_PARAM).lt(interval.getDelDateString()))     
         .filter(QueryBuilders.boolQuery().mustNot(QueryBuilders.matchQuery(ACCOUNT_TYPE, "PREPAID")))
         .must(QueryBuilders.boolQuery()
                       .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.matchQuery(ACCOUNT_TYPE,"POSTPAID")))
                       .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.matchQuery(PRODUCT_NAME, "SIM"))).minimumShouldMatch(1));

this is satisfying the conidtion to fetch all the non PREPAID orders and non POSPAID whose product name is not SIM.

Upvotes: 0

Val
Val

Reputation: 217304

You have some boolean logic issues. Try this way:

QueryBuilder query = QueryBuilders.boolQuery()
         .filter(QueryBuilders.rangeQuery(LASTUPDATED_TIMESTAMP_PARAM).lt(interval.getDelDateString()))             
         .minimumShouldMatch(1)
         .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.matchQuery("ACCOUNT_TYPE", "PREPAID")))
         .should(QueryBuilders.boolQuery()    
                    .mustNot(QueryBuilders.matchQuery("ACCOUNT_TYPE","POSTPAID"))
                    .mustNot(QueryBuilders.matchQuery("PRODUCT_NAME", "SIM")));

Upvotes: 1

Related Questions