# Count of list in document (sql group by) **URL:** \ **Category:** Elasticsearch\ **Created:** [April 28, 2014, 10:10pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243 "2014-04-28T22:10:12Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 10:10pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/1 "2014-04-28T22:10:12Z")
Hi If I have a list in each document and I want to get the document id plus the size of the list eg POST /articles/article {"title" : "One", "tags" : ["foo"]} POST /articles/article {"title" : "Two", "tags" : ["foo", "bar"]} POST /articles/article {"title" : "Three", "tags" : ["foo", "bar", "baz"]} So One will have a count of: 1 article Two has a count of: 2 Three has a count of: 3 Is that something the facets can do? Actually I want the result ordered by count and indexed by document id so something like: - "\_id" , count\* "osweUJq0Ts6BrlIGETLXpA", 1 "Fi8eBgZHQjOLWQwQESJqCw", 2 "EQBHVcPuSce1kNUZXhFtMw", 3 In other words retrieve all documents along with "size of tags" order by size of tags (ascending or descending) Thanks in advance -- 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/74be4adf-d72f-4033-bb40-40f45d8c83ee%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/74be4adf-d72f-4033-bb40-40f45d8c83ee%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [April 28, 2014, 10:18pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/2 "2014-04-28T22:18:35Z")
I think you should index it as a value in your document. POST /articles/article {"title" : "One", "tags" : ["foo"], "size": 1} POST /articles/article {"title" : "Two", "tags" : ["foo", "bar"], "size": 2} POST /articles/article {"title" : "Three", "tags" : ["foo", "bar", "baz"], "size": 3} It will be really efficient. That said, you could probably use a field script to compute it live: [http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-request-script-fields.html#search-request-script-fields](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-request-script-fields.html#search-request-script-fields) and this for sorting: [http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-request-sort.html#\_script\_based\_sorting](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-request-sort.html#_script_based_sorting) HTH -- David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com) @dadoonet | @elasticsearchfr Le 29 avril 2014 à 00:10:17, [eunever32@gmail.com](mailto:eunever32@gmail.com) ([eunever32@gmail.com](mailto:eunever32@gmail.com)) a écrit: Hi If I have a list in each document and I want to get the document id plus the size of the list eg POST /articles/article {"title" : "One", "tags" : ["foo"]} POST /articles/article {"title" : "Two", "tags" : ["foo", "bar"]} POST /articles/article {"title" : "Three", "tags" : ["foo", "bar", "baz"]} So One will have a count of: 1 article Two has a count of: 2 Three has a count of: 3 Is that something the facets can do? Actually I want the result ordered by count and indexed by document id so something like: "\_id" , count "osweUJq0Ts6BrlIGETLXpA", 1 "Fi8eBgZHQjOLWQwQESJqCw", 2 "EQBHVcPuSce1kNUZXhFtMw", 3 In other words retrieve all documents along with "size of tags" order by size of tags (ascending or descending) Thanks in advance -- 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/74be4adf-d72f-4033-bb40-40f45d8c83ee%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/74be4adf-d72f-4033-bb40-40f45d8c83ee%40googlegroups.com). 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/etPan.535ed3bb.614fd4a1.16bd1%40MacBook-Air-de-David.local](https://groups.google.com/d/msgid/elasticsearch/etPan.535ed3bb.614fd4a1.16bd1%40MacBook-Air-de-David.local). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 10:43pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/3 "2014-04-28T22:43:23Z")
Thanks David but the list is being appended during bulk indexing. To calculate the size on each update would slow things down. Is there no equivalent to select id, length (tags) order by length (tags) desc; ? Thanks. -- 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/44036826-6f41-48e9-bf0f-9fe8dc339dbc%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/44036826-6f41-48e9-bf0f-9fe8dc339dbc%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [April 28, 2014, 10:47pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/4 "2014-04-28T22:47:47Z")
Do you append it using a script in bulk request? If so you could also increment size of the list by script I guess. May be something like this could help? [http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-metrics-valuecount-aggregation.html](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-metrics-valuecount-aggregation.html) Unsure though. But you won't be able to sort based on that. -- David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com) @dadoonet | @elasticsearchfr Le 29 avril 2014 à 00:43:27, [eunever32@gmail.com](mailto:eunever32@gmail.com) ([eunever32@gmail.com](mailto:eunever32@gmail.com)) a écrit: Thanks David but the list is being appended during bulk indexing. To calculate the size on each update would slow things down. Is there no equivalent to select id, length (tags) order by length (tags) desc; ? Thanks. -- 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/44036826-6f41-48e9-bf0f-9fe8dc339dbc%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/44036826-6f41-48e9-bf0f-9fe8dc339dbc%40googlegroups.com). 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/etPan.535eda93.5072367.16bd1%40MacBook-Air-de-David.local](https://groups.google.com/d/msgid/elasticsearch/etPan.535eda93.5072367.16bd1%40MacBook-Air-de-David.local). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:02pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/5 "2014-04-28T23:02:57Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/2e1b9e8f-df79-4fb4-80f3-6fa27d357fcd%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2e1b9e8f-df79-4fb4-80f3-6fa27d357fcd%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/6 "2014-04-28T23:11:55Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/1557bd63-14bb-455a-92fa-505bb3df1e1b%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1557bd63-14bb-455a-92fa-505bb3df1e1b%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/7 "2014-04-28T23:11:55Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/1224d6d2-266c-4a15-9e47-1c7d30d3a52c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1224d6d2-266c-4a15-9e47-1c7d30d3a52c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/8 "2014-04-28T23:11:55Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/1108824d-3701-4fc5-8edd-738956e057aa%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1108824d-3701-4fc5-8edd-738956e057aa%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/9 "2014-04-28T23:11:55Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/808e8ae4-dca9-47ec-820c-c6e2582e6b00%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/808e8ae4-dca9-47ec-820c-c6e2582e6b00%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/10 "2014-04-28T23:11:56Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/1570499d-6df2-4890-8de4-b47e95428b4c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1570499d-6df2-4890-8de4-b47e95428b4c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/11 "2014-04-28T23:11:56Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/9de6c438-44df-403e-8714-0989542f70c1%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9de6c438-44df-403e-8714-0989542f70c1%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/12 "2014-04-28T23:11:57Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/660c5916-9f0a-4aca-8834-9151461f127e%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/660c5916-9f0a-4aca-8834-9151461f127e%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/13 "2014-04-28T23:11:57Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/7d0e0e4f-7bcd-4571-975e-ca6d90c4f844%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/7d0e0e4f-7bcd-4571-975e-ca6d90c4f844%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/14 "2014-04-28T23:11:58Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/c88b863d-4428-41c3-969e-8e19dad265f6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c88b863d-4428-41c3-969e-8e19dad265f6%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 28, 2014, 11:11pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/15 "2014-04-28T23:11:58Z")
Yes you're right I thought of that. But I just want to get some counts Not a permanent count. Not a development change. I might want to get other counts another day. -- 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/1aca0d98-cb82-4911-8e64-03054154509c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1aca0d98-cb82-4911-8e64-03054154509c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 29, 2014, 8:25pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/16 "2014-04-29T20:25:17Z")
Folks Apologies about the multiple posts yesterday. A problem with the mobile device I borrowed, (not android). Anyway what I was getting at is lets say you have rows in a database Then what I want to do is: select col1, count(col2) group by col1 Is this an option in Elasticsearch ? If I have to do something like: create index2 as select col1, count(col2) group by col1 then okay I will do that in Elasticsearch..... How do I get these statistics on the data? Thanks. On Tuesday, April 29, 2014 12:11:58 AM UTC+1, [eune...@gmail.com](mailto:eune...@gmail.com) wrote: > Yes you're right I thought of that. > > But I just want to get some counts > > Not a permanent count. Not a development change. I might want to get other > counts another day. -- 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/7614d210-5dee-4770-98d7-6121c683efd9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/7614d210-5dee-4770-98d7-6121c683efd9%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 29, 2014, 9:27pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/17 "2014-04-29T21:27:51Z")
Me again;) This looks like what I need but can't get it to work... Here's the query: GET /articles/\_search { "aggs": { "group\_by\_id": { "terms": { "field": "id", "size":0 }, "aggs": { "sum\_count": { "value\_count": { "field": "tags" } } } } } } Here's the data: POST /articles/article {"title" : "One", "tags" : ["foo"]} POST /articles/article {"title" : "Two", "tags" : ["foo", "bar"]} POST /articles/article {"title" : "Three", "tags" : ["foo", "bar", "baz"]} And what I hope to achieve is id1, 1 id2, 2 id3,3 in other words the count of tags in the list. Thanks. On Tuesday, April 29, 2014 9:25:17 PM UTC+1, [eune...@gmail.com](mailto:eune...@gmail.com) wrote: > Folks > > Apologies about the multiple posts yesterday. A problem with the mobile > device I borrowed, (not android). > > Anyway what I was getting at is lets say you have rows in a database > > Then what I want to do is: > select col1, count(col2) group by col1 > > Is this an option in Elasticsearch ? > > If I have to do something like: create index2 as select col1, count(col2) > group by col1 then okay I will do that in Elasticsearch..... > > How do I get these statistics on the data? > > Thanks. > > On Tuesday, April 29, 2014 12:11:58 AM UTC+1, [eune...@gmail.com](mailto:eune...@gmail.com) wrote: > > > Yes you're right I thought of that. > > > > But I just want to get some counts > > > > Not a permanent count. Not a development change. I might want to get > > other counts another day. -- 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/0e6fd36f-5f01-4aa2-bff5-a4388a18c24b%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0e6fd36f-5f01-4aa2-bff5-a4388a18c24b%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![eunever32](https://avatars.discourse-cdn.com/v4/letter/e/e68b1a/32.png) [@eunever32](https://discuss.elastic.co/u/eunever32)\ **Post date:** [April 29, 2014, 9:54pm UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/18 "2014-04-29T21:54:17Z")
Hey: I figured out a way to do what I want: GET /articles/\_search { ``` "query" : { "match_all" : {} }, "sort" : { "_script" : { "script" : "doc['tags'].values.length", "type" : "number", "order" : "desc" } } ``` } On Tuesday, April 29, 2014 10:27:51 PM UTC+1, [eune...@gmail.com](mailto:eune...@gmail.com) wrote: > Me again;) > > This looks like what I need but can't get it to work... > > Here's the query: > > GET /articles/\_search > { > "aggs": { > "group\_by\_id": { > "terms": { > "field": "id", > "size":0 > }, > "aggs": { > "sum\_count": { > "value\_count": { > "field": "tags" > } > } > } > } > } > } > > Here's the data: > > POST /articles/article > {"title" : "One", "tags" : ["foo"]} > POST /articles/article > {"title" : "Two", "tags" : ["foo", "bar"]} > POST /articles/article > {"title" : "Three", "tags" : ["foo", "bar", "baz"]} > > And what I hope to achieve is > id1, 1 > id2, 2 > id3,3 > > in other words the count of tags in the list. > > Thanks. > > On Tuesday, April 29, 2014 9:25:17 PM UTC+1, [eune...@gmail.com](mailto:eune...@gmail.com) wrote: > > > Folks > > > > Apologies about the multiple posts yesterday. A problem with the mobile > > device I borrowed, (not android). > > > > Anyway what I was getting at is lets say you have rows in a database > > > > Then what I want to do is: > > select col1, count(col2) group by col1 > > > > Is this an option in Elasticsearch ? > > > > If I have to do something like: create index2 as select col1, count(col2) > > group by col1 then okay I will do that in Elasticsearch..... > > > > How do I get these statistics on the data? > > > > Thanks. > > > > On Tuesday, April 29, 2014 12:11:58 AM UTC+1, [eune...@gmail.com](mailto:eune...@gmail.com) wrote: > > > > > Yes you're right I thought of that. > > > > > > But I just want to get some counts > > > > > > Not a permanent count. Not a development change. I might want to get > > > other counts another day. -- 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/fb955f0a-ff85-4869-b845-a58f321fcbf0%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/fb955f0a-ff85-4869-b845-a58f321fcbf0%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, 1:32am UTC](https://discuss.elastic.co/t/count-of-list-in-document-sql-group-by/17243/19 "2017-07-06T01:32:38Z")