conditional Query elastic search using java client not working-CodePudding

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

if accountType is not "Prepaid" OR
if accountType is not "Postpaid" AND ProductName is not "SIM" AND
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}}

CodePudding user response：

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")));

CodePudding user response：

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.