# Aggregate return all data **URL:** \ **Category:** Elasticsearch\ **Created:** [April 1, 2020, 1:10pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053 "2020-04-01T13:10:33Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![hariskhalique](https://avatars.discourse-cdn.com/v4/letter/h/a3d4f5/32.png) [@hariskhalique](https://discuss.elastic.co/u/hariskhalique)\ **Post date:** [April 1, 2020, 1:10pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/1 "2020-04-01T13:10:33Z")
Hi, Following is my query GET product\_detail\_info/\_search { "query": { "multi\_match": { "query": "a", "fields": ["item\_name", "description","cat\_name","item\_details"] } }, "aggs": { "search\_result": { "terms": { "field": "product\_id" } } } } I am new to elasticsearch its my assumption it should work like group by clause in sql but its returning all data ( multiple record with same product id ) following are the response. ``` [ { "_index" : "product_detail_info", "_type" : "doc", "_id" : "41638564138017228075", "_score" : 8.214151, "_source" : { "brand_id" : 232, "item_id" : 8564, "item_details" : """ """, "document_id" : "41638564138017228075", "item_image" : "5a9932bd107ad.png", "pro_updated_date" : "2020-03-04T19:23:12.000Z", "image_sort_order" : 1, "image_product_name" : "5a97d4efba0e6.jpg", "item_size_id" : 8075, "updated_at" : "2020-03-04T19:23:12.000Z", "control_number" : "STS6689", "@version" : "1", "item_color_id" : 1722, "description" : "

A godet panel adds volume to this slim A-line denim mini that’s equally modern and preppy.

", "image_id" : 4163, "item_color_image" : "5a97d4b41e9f9.jpg", "image_title" : "Sugar Lips_STS6689_Pink_WR_R_FLAT-375x475", "item_v4_size_title" : "S", "item_sku" : "SS8564", "product_id" : 1380, "shopify_product_id" : 3958192767043, "item_name" : "Sugar Lips Put a Bow on It Skirt", "cat_shopify_collection_id" : null, "item_price" : 56.0, "display_product_color_id" : 1722, "cat_name" : "Mini", "@timestamp" : "2020-04-01T11:50:03.562Z", "gender" : "f" } }, { "_index" : "product_detail_info", "_type" : "doc", "_id" : "41638565138017228076", "_score" : 7.8401, "_source" : { "brand_id" : 232, "item_id" : 8565, "item_details" : """ """, "document_id" : "41638565138017228076", "item_image" : "5a9932bb3fd65.png", "pro_updated_date" : "2020-03-04T19:23:12.000Z", "image_sort_order" : 1, "image_product_name" : "5a97d4efba0e6.jpg", "item_size_id" : 8076, "updated_at" : "2020-03-04T19:23:12.000Z", "control_number" : "STS6689", "@version" : "1", "item_color_id" : 1722, "description" : "

A godet panel adds volume to this slim A-line denim mini that’s equally modern and preppy.

", "image_id" : 4163, "item_color_image" : "5a97d4b41e9f9.jpg", "image_title" : "Sugar Lips_STS6689_Pink_WR_R_FLAT-375x475", "item_v4_size_title" : "M", "item_sku" : "SS8565", "product_id" : 1380, "shopify_product_id" : 3958192767043, "item_name" : "Sugar Lips Put a Bow on It Skirt", "cat_shopify_collection_id" : null, "item_price" : 56.0, "display_product_color_id" : 1722, "cat_name" : "Mini", "@timestamp" : "2020-04-01T11:50:03.562Z", "gender" : "f" } ] ``` help will be highly appreciated. I was expected it will work like group by clause. thanks ---
**Author:** ![Mike.Barretta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mike.barretta/32/16688_2.png) [@Mike.Barretta](https://discuss.elastic.co/u/Mike.Barretta)\ **Post date:** [April 1, 2020, 1:49pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/2 "2020-04-01T13:49:54Z")
@hariskhalique The response you posted is incomplete, as I'd expect your aggregation results are further down in the response. Whenever you run a search, Elasticsearch will respond with search hits. So your query will give you both the top 10 (by default) matching records as well as the aggregation you requested. Try running the query with `size=0` like: ```auto GET product_detail_info/_search?size=0 ``` or ```auto GET product_detail_info/_search { "size": 0, "query": { ... } ``` That way, the only response you'll get is the aggregation, which should have one bucket per `product_id` with a count of the matching results in each one. If you want matching documents to be returned with each bucket, look at including a [top hits aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html) under the terms agg or check out [field collapsing](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/search-request-body.html#request-body-search-collapse) ---
**Author:** ![hariskhalique](https://avatars.discourse-cdn.com/v4/letter/h/a3d4f5/32.png) [@hariskhalique](https://discuss.elastic.co/u/hariskhalique)\ **Post date:** [April 1, 2020, 2:39pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/3 "2020-04-01T14:39:42Z")
Stille having multiple record with same product ids > ``` > GET product_detail_info/_search > { > "aggs": { > "results": { > "terms": { > "field": "product_id", > "size": 10 > },"aggs": { > "result": { > "top_hits": { > "_source": {"includes": [ > "item_name", > "cat_name", > "product_id" > ]} > } > } > } > } > } > } > > ``` return data is ``` { "_index" : "product_detail_info", "_type" : "doc", "_id" : "1715781892633110038286162314", "_score" : 1.0, "_source" : { "cat_name" : "T-Shirts", "product_id" : 31100, "item_name" : "Kimber Slinky Jersey T-Shirt" } }, { "_index" : "product_detail_info", "_type" : "doc", "_id" : "1715911892633110038286162314", "_score" : 1.0, "_source" : { "cat_name" : "T-Shirts", "product_id" : 31100, "item_name" : "Kimber Slinky Jersey T-Shirt" } } ``` any suggestion. ---
**Author:** ![Mike.Barretta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mike.barretta/32/16688_2.png) [@Mike.Barretta](https://discuss.elastic.co/u/Mike.Barretta)\ **Post date:** [April 1, 2020, 2:41pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/4 "2020-04-01T14:41:05Z")
@hariskhalique you still need to set `size=0`. ```auto GET product_detail_info/_search { "size": 0, "aggs": { "results": { "terms": { "field": "product_id", "size": 10 },"aggs": { "result": { "top_hits": { "_source": {"includes": [ "item_name", "cat_name", "product_id" ]} } } } } } } ``` ---
**Author:** ![hariskhalique](https://avatars.discourse-cdn.com/v4/letter/h/a3d4f5/32.png) [@hariskhalique](https://discuss.elastic.co/u/hariskhalique)\ **Post date:** [April 1, 2020, 2:46pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/5 "2020-04-01T14:46:26Z")
Thanks It works do we have any way to add multiple fileds aggs ---
**Author:** ![Mike.Barretta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mike.barretta/32/16688_2.png) [@Mike.Barretta](https://discuss.elastic.co/u/Mike.Barretta)\ **Post date:** [April 1, 2020, 2:53pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/6 "2020-04-01T14:53:05Z")
Not sure what you mean. You can get multiple aggs in one request like ```auto GET my-index/_search?size=0 { "aggs": { "1": { ... }, "2": { ... }, } } ``` or do nested aggs like you're doing with `top_hits` ```auto GET my-index/_search?size=0 { "aggs": { "1": { ... , "aggs": { "2": { ... } } } } ``` Either what you had in mind? ---
**Author:** ![hariskhalique](https://avatars.discourse-cdn.com/v4/letter/h/a3d4f5/32.png) [@hariskhalique](https://discuss.elastic.co/u/hariskhalique)\ **Post date:** [April 1, 2020, 2:57pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/7 "2020-04-01T14:57:37Z")
Man you are awesome. One final question sql we wrote column1 = column2 how we can do that in ES. All above answer helps me a lot I am really thanks full to you. ---
**Author:** ![Mike.Barretta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mike.barretta/32/16688_2.png) [@Mike.Barretta](https://discuss.elastic.co/u/Mike.Barretta)\ **Post date:** [April 1, 2020, 3:04pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/8 "2020-04-01T15:04:03Z")
Happy to help! Think the only way to do what you want in a single query is to do a [scripted query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-script-query.html) ```auto GET /_search { "query": { "bool" : { "filter" : { "script" : { "script" : { "source": "doc['column1'].value == doc['column2'].value", "lang": "painless" } } } } } } ``` ---
**Author:** ![hariskhalique](https://avatars.discourse-cdn.com/v4/letter/h/a3d4f5/32.png) [@hariskhalique](https://discuss.elastic.co/u/hariskhalique)\ **Post date:** [April 1, 2020, 4:28pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/9 "2020-04-01T16:28:15Z")
One more question how we use wild card. I try like this > ``` > "query": { > "wildcard": { > "item_name": { > "value": "*Iko*", > "boost": 1.0, > "rewrite": "constant_score" > } > } > > ``` and like this ``` "match": { "item_name": "*Ik" } ``` and like this ``` "regexp": { "item_name": "^Iko" } ``` data have value ``` { "_index" : "product_detail_info", "_type" : "doc", "_id" : "1733951907733120138583162807", "_score" : 6.0950193, "_source" : { "image_product_name" : "5dd90d61e82a7.jpg", "item_sku" : "SS190773", "item_id" : 190773, "cat_name" : "T-Shirts", "product_id" : 31201, "item_name" : "Ikonik Karl Pocket T-Shirt" } ``` and I want to apply same search on more then one fields ---
**Author:** ![Mike.Barretta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mike.barretta/32/16688_2.png) [@Mike.Barretta](https://discuss.elastic.co/u/Mike.Barretta)\ **Post date:** [April 1, 2020, 4:37pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/10 "2020-04-01T16:37:27Z")
Please create another question for this: it'll help others find it when looking for similar help! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 29, 2020, 4:37pm UTC](https://discuss.elastic.co/t/aggregate-return-all-data/226053/11 "2020-04-29T16:37:38Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.