# Elastic search - group by query on array **URL:** \ **Category:** Elasticsearch\ **Created:** [July 8, 2014, 10:53am UTC](https://discuss.elastic.co/t/elastic-search-group-by-query-on-array/18525 "2014-07-08T10:53:18Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![K\_Samanth\_Kumar\_Redd](https://avatars.discourse-cdn.com/v4/letter/k/e9c0ed/32.png) [@K\_Samanth\_Kumar\_Redd](https://discuss.elastic.co/u/K_Samanth_Kumar_Redd)\ **Post date:** [July 8, 2014, 10:53am UTC](https://discuss.elastic.co/t/elastic-search-group-by-query-on-array/18525/1 "2014-07-08T10:53:18Z")
Hi, I am working on elasticsearch for last 2 months. It is really providing awesome searching capabilities, good json structure documents etc... Currently I am stuck up with the problem on How to write group by query and get the data. Ex:- In this example company, prod\_type are defined as 'not\_analyzed' Example Documents: {"company":"ABC","orders":[{"order\_no":"OL1", "prod\_type" : "OLP", "price":20}, {"order\_no":"OL2", "prod\_type" : "OLP", "price":50}, {"order\_no":"OL3", "prod\_type" : "GLP", "price":100} ]} {"company":"XYZ","orders":[{"order\_no":"OL10", "prod\_type" : "GLP", "price":50}, {"order\_no":"OL20", "prod\_type" : "OLP", "price":80}, {"order\_no":"OL30", "prod\_type" : "GLP", "price":100} ]} My Requirement: I want the elasticsearch query to get the count, sum(price) based on prod\_type SQL Comparision Qry: SELECT COUNT(\*), SUM(PRICE) FROM TABLE\_NAME GROUP BY PROD\_TYPE Can anyone please help me this? Please let me know if you need more information. Thanks, Samanth -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![vineeth\_mohan\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vineeth_mohan_2/32/747_2.png) [@vineeth\_mohan\_2](https://discuss.elastic.co/u/vineeth_mohan_2)\ **Post date:** [July 8, 2014, 1:32pm UTC](https://discuss.elastic.co/t/elastic-search-group-by-query-on-array/18525/2 "2014-07-08T13:32:17Z")
Hi Samanth , First you will need to make that array into nested type. - > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. Then you need to do a 2 level agg with term aggregation at parent on field prod\_type and sum aggregation on price field. Term aggregation - > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. Sum aggregation - > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. Thanks Vineeth On Tue, Jul 8, 2014 at 4:23 PM, K.Samanth Kumar Reddy \< [samanthkumar.k@gmail.com](mailto:samanthkumar.k@gmail.com)\> wrote: > Hi, > > I am working on elasticsearch for last 2 months. It is really providing > awesome searching capabilities, good json structure documents etc... > Currently I am stuck up with the problem on How to write group by query > and get the data. > > Ex:- In this example company, prod\_type are defined as 'not\_analyzed' > > Example Documents: > > {"company":"ABC","orders":[{"order\_no":"OL1", "prod\_type" : "OLP", > "price":20}, {"order\_no":"OL2", "prod\_type" : "OLP", "price":50}, > {"order\_no":"OL3", "prod\_type" : "GLP", "price":100} ]} > > {"company":"XYZ","orders":[{"order\_no":"OL10", "prod\_type" : "GLP", > "price":50}, {"order\_no":"OL20", "prod\_type" : "OLP", "price":80}, > {"order\_no":"OL30", "prod\_type" : "GLP", "price":100} ]} > > My Requirement: I want the elasticsearch query to get the count, > sum(price) based on prod\_type > SQL Comparision Qry: SELECT COUNT(\*), SUM(PRICE) FROM TABLE\_NAME GROUP BY > PROD\_TYPE > > Can anyone please help me this? > > Please let me know if you need more information. > > Thanks, > Samanth > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5n1PKoXYgg0Y9y-6K%3DBN3UX920DYV%2BEpv9wGgPZZOgxsg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5n1PKoXYgg0Y9y-6K%3DBN3UX920DYV%2BEpv9wGgPZZOgxsg%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![K\_Samanth\_Kumar\_Redd](https://avatars.discourse-cdn.com/v4/letter/k/e9c0ed/32.png) [@K\_Samanth\_Kumar\_Redd](https://discuss.elastic.co/u/K_Samanth_Kumar_Redd)\ **Post date:** [July 8, 2014, 4:56pm UTC](https://discuss.elastic.co/t/elastic-search-group-by-query-on-array/18525/3 "2014-07-08T16:56:53Z")
Hi Vineeth, Thank you very much. I will try and let you know. Thanks, Samanth On Tuesday, July 8, 2014 4:23:18 PM UTC+5:30, K.Samanth Kumar Reddy wrote: > Hi, > > I am working on elasticsearch for last 2 months. It is really providing > awesome searching capabilities, good json structure documents etc... > Currently I am stuck up with the problem on How to write group by query > and get the data. > > Ex:- In this example company, prod\_type are defined as 'not\_analyzed' > > Example Documents: > > {"company":"ABC","orders":[{"order\_no":"OL1", "prod\_type" : "OLP", > "price":20}, {"order\_no":"OL2", "prod\_type" : "OLP", "price":50}, > {"order\_no":"OL3", "prod\_type" : "GLP", "price":100} ]} > > {"company":"XYZ","orders":[{"order\_no":"OL10", "prod\_type" : "GLP", > "price":50}, {"order\_no":"OL20", "prod\_type" : "OLP", "price":80}, > {"order\_no":"OL30", "prod\_type" : "GLP", "price":100} ]} > > My Requirement: I want the elasticsearch query to get the count, > sum(price) based on prod\_type > SQL Comparision Qry: SELECT COUNT(\*), SUM(PRICE) FROM TABLE\_NAME GROUP BY > PROD\_TYPE > > Can anyone please help me this? > > Please let me know if you need more information. > > Thanks, > Samanth -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/d02a3c0b-48f6-4be3-b4b9-a4f54c4f4fcd%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d02a3c0b-48f6-4be3-b4b9-a4f54c4f4fcd%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![K\_Samanth\_Kumar\_Redd](https://avatars.discourse-cdn.com/v4/letter/k/e9c0ed/32.png) [@K\_Samanth\_Kumar\_Redd](https://discuss.elastic.co/u/K_Samanth_Kumar_Redd)\ **Post date:** [July 9, 2014, 3:51am UTC](https://discuss.elastic.co/t/elastic-search-group-by-query-on-array/18525/4 "2014-07-09T03:51:11Z")
Thank you very much. Its working. Thanks, Samanth On Tuesday, July 8, 2014 4:23:18 PM UTC+5:30, K.Samanth Kumar Reddy wrote: > Hi, > > I am working on elasticsearch for last 2 months. It is really providing > awesome searching capabilities, good json structure documents etc... > Currently I am stuck up with the problem on How to write group by query > and get the data. > > Ex:- In this example company, prod\_type are defined as 'not\_analyzed' > > Example Documents: > > {"company":"ABC","orders":[{"order\_no":"OL1", "prod\_type" : "OLP", > "price":20}, {"order\_no":"OL2", "prod\_type" : "OLP", "price":50}, > {"order\_no":"OL3", "prod\_type" : "GLP", "price":100} ]} > > {"company":"XYZ","orders":[{"order\_no":"OL10", "prod\_type" : "GLP", > "price":50}, {"order\_no":"OL20", "prod\_type" : "OLP", "price":80}, > {"order\_no":"OL30", "prod\_type" : "GLP", "price":100} ]} > > My Requirement: I want the elasticsearch query to get the count, > sum(price) based on prod\_type > SQL Comparision Qry: SELECT COUNT(\*), SUM(PRICE) FROM TABLE\_NAME GROUP BY > PROD\_TYPE > > Can anyone please help me this? > > Please let me know if you need more information. > > Thanks, > Samanth -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c1ca78ae-24ad-4afa-bbcb-9d17bb0f6fcf%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1ca78ae-24ad-4afa-bbcb-9d17bb0f6fcf%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Raja\_Sekhar\_Bhamidip](https://avatars.discourse-cdn.com/v4/letter/r/958977/32.png) [@Raja\_Sekhar\_Bhamidip](https://discuss.elastic.co/u/Raja_Sekhar_Bhamidip)\ **Post date:** [October 30, 2014, 10:37am UTC](https://discuss.elastic.co/t/elastic-search-group-by-query-on-array/18525/5 "2014-10-30T10:37:00Z")
Hi Samanth, I have started working on elasticsearch recently. I'm trying to get some result exactly like what you have tried. But the problem is I'm getting sum value same for all buckets ( Total price of the document where the prod type exists ) For your example the search json I wrote is { "aggs": { "prod\_type": { "terms": { "field": "orders.prod\_type" }, "aggs": { "total\_price": { "sum": { "field": "price" } } } } } } The result I got is "aggregations": { "prod\_type": { "buckets": [ { "key": "glp", "doc\_count": 2, "total\_price": { "value": 400 } }, { "key": "olp", "doc\_count": 2, "total\_price": { "value": 400 } } ] } } Could you please help me out in this? Regards, Raja On Wednesday, July 9, 2014 9:21:11 AM UTC+5:30, K.Samanth Kumar Reddy wrote: > Thank you very much. Its working. > > Thanks, > Samanth > > On Tuesday, July 8, 2014 4:23:18 PM UTC+5:30, K.Samanth Kumar Reddy wrote: > > > Hi, > > > > I am working on elasticsearch for last 2 months. It is really providing > > awesome searching capabilities, good json structure documents etc... > > Currently I am stuck up with the problem on How to write group by query > > and get the data. > > > > Ex:- In this example company, prod\_type are defined as 'not\_analyzed' > > > > Example Documents: > > > > {"company":"ABC","orders":[{"order\_no":"OL1", "prod\_type" : "OLP", > > "price":20}, {"order\_no":"OL2", "prod\_type" : "OLP", "price":50}, > > {"order\_no":"OL3", "prod\_type" : "GLP", "price":100} ]} > > > > {"company":"XYZ","orders":[{"order\_no":"OL10", "prod\_type" : "GLP", > > "price":50}, {"order\_no":"OL20", "prod\_type" : "OLP", "price":80}, > > {"order\_no":"OL30", "prod\_type" : "GLP", "price":100} ]} > > > > My Requirement: I want the elasticsearch query to get the count, > > sum(price) based on prod\_type > > SQL Comparision Qry: SELECT COUNT(\*), SUM(PRICE) FROM TABLE\_NAME GROUP BY > > PROD\_TYPE > > > > Can anyone please help me this? > > > > Please let me know if you need more information. > > > > Thanks, > > Samanth -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/192cf64d-c704-4dc5-ac49-f896c10b3e26%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/192cf64d-c704-4dc5-ac49-f896c10b3e26%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 12:53am UTC](https://discuss.elastic.co/t/elastic-search-group-by-query-on-array/18525/6 "2017-07-06T00:53:01Z")