# Elasticsearch bool query formation with multiple must clause
**URL:** https://discuss.elastic.co/t/elasticsearch-bool-query-formation-with-multiple-must-clause/97509
**Category:** Elasticsearch
**Created:** [August 18, 2017, 4:14am UTC](https://discuss.elastic.co/t/elasticsearch-bool-query-formation-with-multiple-must-clause/97509 "2017-08-18T04:14:20Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Milind\_Karandikar](https://discuss.elastic.co/u/Milind_Karandikar)
#### Post date: [August 18, 2017, 4:14am UTC](https://discuss.elastic.co/t/elasticsearch-bool-query-formation-with-multiple-must-clause/97509/1 "2017-08-18T04:14:20Z")
I have a query like the following -
```
{
"query": {
"bool": {
"must": {
"bool" : { "should": [
{ "match": { "camp_id": "Elasticsearch" }},
{ "match": { "camp_id": "Solr" }} ] }
},
"must": {
"bool" : { "should": [
{ "match": { "ad_id": "Elastic" }},
{ "match": { "ad_id": "dummy" }} ] }
},
"must_not": { "match": {"authors": "radu gheorge" }},
.....
.....
}
}
}
```
In short, (camp\_id = 'elasticsearch' or camp\_id = 'solr') AND (ad\_id = 'elasticsearch' or ad\_id = 'solr') ....
After good amount of research, I wrote the following java code -
```
final SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
final BoolQueryBuilder finalBoolQuery = new BoolQueryBuilder();
BoolQueryBuilder campaignBoolQuery = null;
if (campaignIds != null) {
campaignBoolQuery = QueryBuilders.boolQuery();
for (int campaignId : campaignIds) {
campaignBoolQuery.should(QueryBuilders.matchQuery("camp_id", campaignId));
}
}
BoolQueryBuilder creativeBoolQuery = null;
if (creativeIds != null) {
creativeBoolQuery = QueryBuilders.boolQuery();
for (int creativeId : creativeIds) {
creativeBoolQuery.should(QueryBuilders.matchQuery("ad_id", creativeId));
}
}
finalBoolQuery.must(campaignBoolQuery);
finalBoolQuery.must(creativeBoolQuery);
searchSourceBuilder.query(finalBoolQuery).size(9999);
System.out.println(searchSourceBuilder.toString());
```
With the above code, I expected that I would have 1 must clause for 'camp\_id' and another 1 for 'ad\_id' but following is what I got -
```
{
"size" : 9999,
"query" : {
"bool" : {
"must" : [
{
"bool" : {
"should" : [
{
"match" : {
"camp_id" : {
"query" : 1,
"operator" : "OR",
"prefix_length" : 0,
"max_expansions" : 50,
"fuzzy_transpositions" : true,
"lenient" : false,
"zero_terms_query" : "NONE",
"boost" : 1.0
}
}
},
{
"match" : {
"camp_id" : {
"query" : 2,
"operator" : "OR",
"prefix_length" : 0,
"max_expansions" : 50,
"fuzzy_transpositions" : true,
"lenient" : false,
"zero_terms_query" : "NONE",
"boost" : 1.0
}
}
}
],
"disable_coord" : false,
"adjust_pure_negative" : true,
"boost" : 1.0
}
},
{
"bool" : {
"should" : [
{
"match" : {
"ad_id" : {
"query" : 1,
"operator" : "OR",
"prefix_length" : 0,
"max_expansions" : 50,
"fuzzy_transpositions" : true,
"lenient" : false,
"zero_terms_query" : "NONE",
"boost" : 1.0
}
}
}
],
"disable_coord" : false,
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
],
"disable_coord" : false,
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
}
```
There is only one must clause which wraps both camp\_id and ad\_id. Can someone please point out what am I missing? I am using elastic search version - 5.5.0 and jest - 2.4.0 as my java client.
---
### Author:  [@Milind\_Karandikar](https://discuss.elastic.co/u/Milind_Karandikar)
#### Post date: [August 18, 2017, 4:24am UTC](https://discuss.elastic.co/t/elasticsearch-bool-query-formation-with-multiple-must-clause/97509/2 "2017-08-18T04:24:01Z")
I am using cerebro as a UI to access elasticsearch cluster. When I tried executing the query from the rest client provided by cerebro, I observed that it gives me an error, stating that - Duplicate must clause. Is it that we can't have multiple must clauses? If not, there should be a way to handle query like SELECT \* FROM campaign WHERE id IN (1,2,3) AND name IN ('a', 'b', 'c'). Can someone help me out
---
### Author:  [@Asura](https://discuss.elastic.co/u/Asura)
#### Post date: [August 18, 2017, 5:14am UTC](https://discuss.elastic.co/t/elasticsearch-bool-query-formation-with-multiple-must-clause/97509/3 "2017-08-18T05:14:20Z")
You cannot have a multiple must clauses,but what you can do is provide multiple queries in a must clause separated by commas,something like this.
```
"query":{
"bool": {
"must": [
{"match": {
"field1" : "value1"
}
},
{
"match": {
"field2": "value2"
}
}
]
}
```
}
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 15, 2017, 5:14am UTC](https://discuss.elastic.co/t/elasticsearch-bool-query-formation-with-multiple-must-clause/97509/4 "2017-09-15T05:14:31Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.