# How execute AND query in elasticsearch and aggregate **URL:** \ **Category:** Elasticsearch\ **Created:** [July 25, 2019, 9:25am UTC](https://discuss.elastic.co/t/how-execute-and-query-in-elasticsearch-and-aggregate/192208 "2019-07-25T09:25:37Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![elasticpatrick](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticpatrick/32/49939_2.png) [@elasticpatrick](https://discuss.elastic.co/u/elasticpatrick)\ **Post date:** [July 25, 2019, 9:25am UTC](https://discuss.elastic.co/t/how-execute-and-query-in-elasticsearch-and-aggregate/192208/1 "2019-07-25T09:25:37Z")
Hello, I have product\_supplier Index which contains products and its suppliers. I have given suppliers names and I want to get the products which are supplied by the given suppliers. For eg. I have given supplier "supplier1" and "supplier2". And I want to get "chips" and "mango" product as it's been supplied by this suppliers. And after that I want to aggregate the product name. Here is the total hits I have. ```auto "hits":[ { "_index": "products_supplier", "_type": "_doc", "_id": "1", "_score": 1.0, "_source": { "product_name": "maggie", "product_supplier": "supplier1" } }, { "_index": "products_supplier", "_type": "_doc", "_id": "2", "_score": 1.0, "_source": { "product_name": "maggie", "product_supplier": "supplier2" } }, { "_index": "products_supplier", "_type": "_doc", "_id": "3", "_score": 1.0, "_source": { "product_name": "potatoes", "product_supplier": "supplier1" } }, { "_index": "products_supplier", "_type": "_doc", "_id": "4", "_score": 1.0, "_source": { "product_name": "chips", "product_supplier": "supplier1" } }, { "_index": "products_supplier", "_type": "_doc", "_id": "5", "_score": 1.0, "_source": { "product_name": "maggie", "product_supplier": "supplier3" } }, { "_index": "products_supplier", "_type": "_doc", "_id": "6", "_score": 1.0, "_source": { "product_name": "mango", "product_supplier": "supplier1" } }, { "_index": "products_supplier", "_type": "_doc", "_id": "7", "_score": 1.0, "_source": { "product_name": "chips", "product_supplier": "supplier2" } }, { "_index": "products_supplier", "_type": "_doc", "_id": "8", "_score": 1.0, "_source": { "product_name": "chips", "product_supplier": "supplier3" } } ] ``` I have query like this ```auto GET /products_supplier/_search { "query": { "bool": { "should": [ {"term": { "product_supplier": { "value": "supplier2" } }}, {"term": { "product_supplier": { "value": "supplier1" } }} ] } }, "aggs": { "product_name": { "terms": { "field": "product_name.keyword" } } } } ``` And I'm getting this ```auto "hits" : { "total" : { "value" : 6, "relation" : "eq" }, "max_score" : 1.3862944, "hits" : [ { "_index" : "products_supplier", "_type" : "_doc", "_id" : "2", "_score" : 1.3862944, "_source" : { "product_name" : "maggie", "product_supplier" : "supplier2" } }, { "_index" : "products_supplier", "_type" : "_doc", "_id" : "7", "_score" : 1.3862944, "_source" : { "product_name" : "chips", "product_supplier" : "supplier2" } }, { "_index" : "products_supplier", "_type" : "_doc", "_id" : "1", "_score" : 0.7985077, "_source" : { "product_name" : "maggie", "product_supplier" : "supplier1" } }, { "_index" : "products_supplier", "_type" : "_doc", "_id" : "3", "_score" : 0.7985077, "_source" : { "product_name" : "potatoes", "product_supplier" : "supplier1" } }, { "_index" : "products_supplier", "_type" : "_doc", "_id" : "4", "_score" : 0.7985077, "_source" : { "product_name" : "chips", "product_supplier" : "supplier1" } }, { "_index" : "products_supplier", "_type" : "_doc", "_id" : "6", "_score" : 0.7985077, "_source" : { "product_name" : "mango", "product_supplier" : "supplier1" } } ] }, "aggregations" : { "product_name" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : "chips", "doc_count" : 2 }, { "key" : "maggie", "doc_count" : 2 }, { "key" : "mango", "doc_count" : 1 }, { "key" : "potatoes", "doc_count" : 1 } ] } } } ``` i only like to get "mango" and "chips" product names ---
**Author:** ![William\_Brafford](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/william_brafford/32/51559_2.png) [@William\_Brafford](https://discuss.elastic.co/u/William_Brafford)\ **Post date:** [August 2, 2019, 8:33pm UTC](https://discuss.elastic.co/t/how-execute-and-query-in-elasticsearch-and-aggregate/192208/2 "2019-08-02T20:33:43Z")
I can think of two approaches filtering your current query so that you only see "chips" and "mango". The first is to update the logic in the boolean query and use a nested boolean query. Currently, your query is searching for documents with a `product_supplier` of `supplier1` or `supplier1`. You can add a clause so that the query searches for documents with `product_supplier` of `supplier1` or `supplier1` **AND** a `product_name` of `chips` or `mango`. That would look like this: ```auto GET /products_supplier/_search { "query": { "bool": { "must": [ { "bool": { "should": [ { "term": { "product_supplier": { "value": "supplier2" } } }, { "term": { "product_supplier": { "value": "supplier1" } } } ] } }, { "bool": { "should": [ { "term": { "product_name": { "value": "mango" } } }, { "term": { "product_name": { "value": "chips" } } } ] } } ] } }, "aggs": { "product_name": { "terms": { "field": "product_name.keyword" } } } } ``` The query returns three documents, and the aggregation runs over those. However, you can simplify that query by replacing the multiple `term` clauses within the `should` clauses with a single [`terms` clause](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html), giving you this: ```auto GET /products_supplier/_search { "query": { "bool": { "must": [ { "terms": { "product_supplier": [ "supplier2", "supplier1" ] } }, { "terms": { "product_name": [ "mango", "chips" ] } } ] } }, "aggs": { "product_name": { "terms": { "field": "product_name.keyword" } } } } ``` To me, this is much easier to read! The second approach you could take is to add a [filter agg](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html) to your aggregation. ```auto GET /products_supplier/_search { "query": { "bool": { "must": [ { "terms": { "product_supplier": [ "supplier2", "supplier1" ] } } ] } }, "aggs": { "product_filter": { "filter": { "terms": { "product_name.keyword": [ "chips", "mango" ] } }, "aggs": { "product_name": { "terms": { "field": "product_name.keyword" } } } } } } ``` Here, the "query" returns six documents, but because of the filter clause, the aggregation only includes the values for `chips` and `mango`. I hope these suggestions help you move forward with Elasticsearch. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 30, 2019, 8:33pm UTC](https://discuss.elastic.co/t/how-execute-and-query-in-elasticsearch-and-aggregate/192208/3 "2019-08-30T20:33:47Z")
Closed on August 30, 2019, 8:33pm UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.