Elasticsearch Java-Play Framework - in query BoolQueryBuilder to write a query

Question

Here is my ES document structure

admin_faq
  - faq_id
  - title
  - is_active

Case 1 Filter have comma separated FAQ id's 2,3,4. Split id's using

        String[] array = assignedCategories.split(",");
        for (String value : array) {
            queryBuilderPost.should(QueryBuilders.termsQuery("faq_id", value));
        }

In case 1 this query working fine for me and only show the faq's where faq id is 2,3,4

Case 2 when I have another filter like is_active so its return the all data where is_active=Y and also related to comma separated id's FAQs

        String[] array = assignedCategories.split(",");
        for (String value : array) {
            queryBuilderPost.should(QueryBuilders.termsQuery("faq_id", value));
        }
        queryBuilderPost.must(QueryBuilders.termsQuery("is_active", isActive));

Note: I wish to only get the data from comma separated ids with active records. You can say it's working like OR condition in MySQL. We need to change it to AND condition I'm trying but not working for me.

Answer 1

You could just replace must with filter.

Alternatively, you need just another layer of bool query.

String[] array = assignedCategories.split(",");
for (String value : array) {
  queryBuilderPost.should(QueryBuilders.termsQuery("faq_id", value));
}
QueryBuilder bool = QueryBuilders.boolQuery();
bool.must(queryBuilderPost);
bool.must(QueryBuilders.termsQuery("is_active", isActive));

And then use bool as your query.