Grouping by non-numeric field

jason · March 16, 2011, 8:00pm

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here: https://github.com/elasticsearch/elasticsearch/issues/#issue/539

Does this feature already exist in the most recent version of ES? If
yes, which facet should I use? I looked at terms and histogram facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your knowledge.

Thank you,
Eugene.

kimchy · March 17, 2011, 9:31am

Terms stats might be what you are after, its in master: Issues · elastic/elasticsearch · GitHub.
On Wednesday, March 16, 2011 at 10:00 PM, eugene wrote:

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here: Issues · elastic/elasticsearch · GitHub

Does this feature already exist in the most recent version of ES? If
yes, which facet should I use? I looked at terms and histogram facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your knowledge.

Thank you,
Eugene.

jason · March 17, 2011, 5:21pm

I run the following query:

{
"query" : {
"match_all": {}
},
"filter" : {
"range" : {
"timestamp" : {
"from" : "2011-01-01",
"to" : "2011-02-02",
"include_lower" : true,
"include_upper" : false
}
}
},
"facets" : {
"url_count_stats" : {
"terms_stats" : {
"key_field" : "url",
"value_field" : "count"
}
}
}
}

and I am getting the exception:

{"error":"SearchPhaseExecutionException[Failed to execute phase
[query], total f
ailure; shardFailures {[B2ehMgT2TVeGgJtOVZLQJw][test][0]:
SearchParseException[[
test][0]: query[ConstantScore(:)],from[-1],size[-1]: Parse Failure
[Failed to
parse source [{ \t"query" : { \t\t"match_all": {}\t },\t"filter
" : {\t\t
"range" : { \t\t\t"timestamp" : { \t\t\t\t"from" :
"2011-01-01", \t\t\t
\t"to" : "2011-02-02", \t\t\t\t"include_lower" : true, \t\t\t\t
"include
_upper" : false \t\t\t}\t\t} \t},\t"facets" : {\t\t"url_count_stats
" : {\t
t\t"terms_stats" : {\t\t\t\t"key_field" : "url",\t\t\t\t
"value_field" :
"count"\t\t\t}\t\t}\t}}]]]; nested: SearchParseException[[test][0]:
query[Cons
tantScore(:)],from[-1],size[-1]: Parse Failure [No facet type found
for [terms
_stats]]]; }{[B2ehMgT2TVeGgJtOVZLQJw][test][1]:
SearchParseException[[test][1]:

On Mar 17, 2:31 am, Shay Banon shay.ba...@elasticsearch.com wrote:

Terms stats might be what you are after, its in master:Issues · elastic/elasticsearch · GitHub.

On Wednesday, March 16, 2011 at 10:00 PM, eugene wrote:

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here:Issues · elastic/elasticsearch · GitHub

Does this feature already exist in the most recent version of ES? If
yes, which facet should I use? I looked at terms and histogram facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your knowledge.

Thank you,
Eugene.- Hide quoted text -

Show quoted text -

jason · March 17, 2011, 6:30pm

Oh, I understand why I am getting this error. Because I need to
download an update of Elasticsearch with termsstats package. Shay,
could you tell me how I can do it from this page:

Thank you,
Eugene.

On Mar 17, 10:21 am, eugene efur...@gmail.com wrote:

I run the following query:

{
"query" : {
"match_all": {}
},
"filter" : {
"range" : {
"timestamp" : {
"from" : "2011-01-01",
"to" : "2011-02-02",
"include_lower" : true,
"include_upper" : false
}
}
},
"facets" : {
"url_count_stats" : {
"terms_stats" : {
"key_field" : "url",
"value_field" : "count"
}
}
}

}

and I am getting the exception:

{"error":"SearchPhaseExecutionException[Failed to execute phase
[query], total f
ailure; shardFailures {[B2ehMgT2TVeGgJtOVZLQJw][test][0]:
SearchParseException[[
test][0]: query[ConstantScore(:)],from[-1],size[-1]: Parse Failure
[Failed to
parse source [{ \t"query" : { \t\t"match_all": {}\t },\t"filter
" : {\t\t
"range" : { \t\t\t"timestamp" : { \t\t\t\t"from" :
"2011-01-01", \t\t\t
\t"to" : "2011-02-02", \t\t\t\t"include_lower" : true, \t\t\t\t
"include
_upper" : false \t\t\t}\t\t} \t},\t"facets" : {\t\t"url_count_stats
" : {\t
t\t"terms_stats" : {\t\t\t\t"key_field" : "url",\t\t\t\t
"value_field" :
"count"\t\t\t}\t\t}\t}}]]]; nested: SearchParseException[[test][0]:
query[Cons
tantScore(:)],from[-1],size[-1]: Parse Failure [No facet type found
for [terms
_stats]]]; }{[B2ehMgT2TVeGgJtOVZLQJw][test][1]:
SearchParseException[[test][1]:

On Mar 17, 2:31 am, Shay Banon shay.ba...@elasticsearch.com wrote:

Terms stats might be what you are after, its in master:Issues · elastic/elasticsearch · GitHub.

On Wednesday, March 16, 2011 at 10:00 PM, eugene wrote:

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here:Issues · elastic/elasticsearch · GitHub

Does this feature already exist in the most recent version of ES? If
yes, which facet should I use? I looked at terms and histogram facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your knowledge.

Thank you,
Eugene.- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -

kimchy · March 17, 2011, 6:33pm

You will need to build elasticsearch from master. Once downloaded / git-cloned, you can run "gradlew release" from the root, and the distribution will eb under build/distributions.

On Thursday, March 17, 2011 at 8:30 PM, eugene wrote:

Oh, I understand why I am getting this error. Because I need to
download an update of Elasticsearch with termsstats package. Shay,
could you tell me how I can do it from this page:

Search: Terms Stats Facet, closes #705. · elastic/elasticsearch@352cb74 · GitHub

Thank you,
Eugene.

On Mar 17, 10:21 am, eugene efur...@gmail.com wrote:

I run the following query:

{
"query" : {
"match_all": {}
},
"filter" : {
"range" : {
"timestamp" : {
"from" : "2011-01-01",
"to" : "2011-02-02",
"include_lower" : true,
"include_upper" : false
}
}
},
"facets" : {
"url_count_stats" : {
"terms_stats" : {
"key_field" : "url",
"value_field" : "count"
}
}
}

}

and I am getting the exception:

{"error":"SearchPhaseExecutionException[Failed to execute phase
[query], total f
ailure; shardFailures {[B2ehMgT2TVeGgJtOVZLQJw][test][0]:
SearchParseException[[
test][0]: query[ConstantScore(:)],from[-1],size[-1]: Parse Failure
[Failed to
parse source [{ \t"query" : { \t\t"match_all": {}\t },\t"filter
" : {\t\t
"range" : { \t\t\t"timestamp" : { \t\t\t\t"from" :
"2011-01-01", \t\t\t
\t"to" : "2011-02-02", \t\t\t\t"include_lower" : true, \t\t\t\t
"include
_upper" : false \t\t\t}\t\t} \t},\t"facets" : {\t\t"url_count_stats
" : {\t
t\t"terms_stats" : {\t\t\t\t"key_field" : "url",\t\t\t\t
"value_field" :
"count"\t\t\t}\t\t}\t}}]]]; nested: SearchParseException[[test][0]:
query[Cons
tantScore(:)],from[-1],size[-1]: Parse Failure [No facet type found
for [terms
_stats]]]; }{[B2ehMgT2TVeGgJtOVZLQJw][test][1]:
SearchParseException[[test][1]:

On Mar 17, 2:31 am, Shay Banon shay.ba...@elasticsearch.com wrote:

Terms stats might be what you are after, its in master:Issues · elastic/elasticsearch · GitHub.

On Wednesday, March 16, 2011 at 10:00 PM, eugene wrote:

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here:Issues · elastic/elasticsearch · GitHub

Does this feature already exist in the most recent version of ES? If
yes, which facet should I use? I looked at terms and histogram facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your knowledge.

Thank you,
Eugene.- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -

jason · March 17, 2011, 9:18pm

Than you, Shay. I built a new version of elasticsearch, and it
contains updated libraries. Is the version 0.16.0 already in maven
repository? I could probably change the version of my elasticsearch
artifact and that would easily work. Please let me know if there is
another workaround, since I am only using maven and want to avoid
adding individual jar files to my project.

On Mar 17, 11:33 am, Shay Banon shay.ba...@elasticsearch.com wrote:

You will need to build elasticsearch from master. Once downloaded / git-cloned, you can run "gradlew release" from the root, and the distribution will eb under build/distributions.

Elasticsearch Platform — Find real-time answers at scale | Elastic

On Thursday, March 17, 2011 at 8:30 PM, eugene wrote:

Oh, I understand why I am getting this error. Because I need to
download an update of Elasticsearch with termsstats package. Shay,
could you tell me how I can do it from this page:

Search: Terms Stats Facet, closes #705. · elastic/elasticsearch@352cb74 · GitHub...

Thank you,
Eugene.

On Mar 17, 10:21 am, eugene efur...@gmail.com wrote:

I run the following query:

{
"query" : {
"match_all": {}
},
"filter" : {
"range" : {
"timestamp" : {
"from" : "2011-01-01",
"to" : "2011-02-02",
"include_lower" : true,
"include_upper" : false
}
}
},
"facets" : {
"url_count_stats" : {
"terms_stats" : {
"key_field" : "url",
"value_field" : "count"
}
}
}

}

and I am getting the exception:

{"error":"SearchPhaseExecutionException[Failed to execute phase
[query], total f
ailure; shardFailures {[B2ehMgT2TVeGgJtOVZLQJw][test][0]:
SearchParseException[[
test][0]: query[ConstantScore(:)],from[-1],size[-1]: Parse Failure
[Failed to
parse source [{ \t"query" : { \t\t"match_all": {}\t },\t"filter
" : {\t\t
"range" : { \t\t\t"timestamp" : { \t\t\t\t"from" :
"2011-01-01", \t\t\t
\t"to" : "2011-02-02", \t\t\t\t"include_lower" : true, \t\t\t\t
"include
_upper" : false \t\t\t}\t\t} \t},\t"facets" : {\t\t"url_count_stats
" : {\t
t\t"terms_stats" : {\t\t\t\t"key_field" : "url",\t\t\t\t
"value_field" :
"count"\t\t\t}\t\t}\t}}]]]; nested: SearchParseException[[test][0]:
query[Cons
tantScore(:)],from[-1],size[-1]: Parse Failure [No facet type found
for [terms
_stats]]]; }{[B2ehMgT2TVeGgJtOVZLQJw][test][1]:
SearchParseException[[test][1]:

On Mar 17, 2:31 am, Shay Banon shay.ba...@elasticsearch.com wrote:

Terms stats might be what you are after, its in master:Issues · elastic/elasticsearch · GitHub.

On Wednesday, March 16, 2011 at 10:00 PM, eugene wrote:

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here:Issues · elastic/elasticsearch · GitHub

Does this feature already exist in the most recent version of ES? If
yes, which facet should I use? I looked at terms and histogram facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your knowledge.

Thank you,
Eugene.- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -

Ivan · March 17, 2011, 9:21pm

There is a version in the Sonatype snapshot repo:

https://oss.sonatype.org/content/repositories/snapshots/org/elasticsearch/elasticsearch/

On Thu, Mar 17, 2011 at 5:18 PM, eugene efurman@gmail.com wrote:

Than you, Shay. I built a new version of elasticsearch, and it
contains updated libraries. Is the version 0.16.0 already in maven
repository? I could probably change the version of my elasticsearch
artifact and that would easily work. Please let me know if there is
another workaround, since I am only using maven and want to avoid
adding individual jar files to my project.

On Mar 17, 11:33 am, Shay Banon shay.ba...@elasticsearch.com wrote:

You will need to build elasticsearch from master. Once downloaded /
git-cloned, you can run "gradlew release" from the root, and the
distribution will eb under build/distributions.

Elasticsearch Platform — Find real-time answers at scale | Elastic

On Thursday, March 17, 2011 at 8:30 PM, eugene wrote:

Oh, I understand why I am getting this error. Because I need to
download an update of Elasticsearch with termsstats package. Shay,
could you tell me how I can do it from this page:

Search: Terms Stats Facet, closes #705. · elastic/elasticsearch@352cb74 · GitHub.
..

Thank you,
Eugene.

On Mar 17, 10:21 am, eugene efur...@gmail.com wrote:

I run the following query:

{
"query" : {
"match_all": {}
},
"filter" : {
"range" : {
"timestamp" : {
"from" : "2011-01-01",
"to" : "2011-02-02",
"include_lower" : true,
"include_upper" : false
}
}
},
"facets" : {
"url_count_stats" : {
"terms_stats" : {
"key_field" : "url",
"value_field" : "count"
}
}
}

}

and I am getting the exception:

{"error":"SearchPhaseExecutionException[Failed to execute phase
[query], total f
ailure; shardFailures {[B2ehMgT2TVeGgJtOVZLQJw][test][0]:
SearchParseException[[
test][0]: query[ConstantScore(:)],from[-1],size[-1]: Parse Failure
[Failed to
parse source [{ \t"query" : { \t\t"match_all": {}\t },\t"filter
" : {\t\t
"range" : { \t\t\t"timestamp" : { \t\t\t\t"from" :
"2011-01-01", \t\t\t
\t"to" : "2011-02-02", \t\t\t\t"include_lower" : true, \t\t\t\t
"include
_upper" : false \t\t\t}\t\t} \t},\t"facets" :
{\t\t"url_count_stats
" : {\t
t\t"terms_stats" : {\t\t\t\t"key_field" : "url",\t\t\t\t
"value_field" :
"count"\t\t\t}\t\t}\t}}]]]; nested: SearchParseException[[test][0]:
query[Cons
tantScore(:)],from[-1],size[-1]: Parse Failure [No facet type found
for [terms
_stats]]]; }{[B2ehMgT2TVeGgJtOVZLQJw][test][1]:
SearchParseException[[test][1]:

On Mar 17, 2:31 am, Shay Banon shay.ba...@elasticsearch.com wrote:

Terms stats might be what you are after, its in master:
Issues · elastic/elasticsearch · GitHub.

On Wednesday, March 16, 2011 at 10:00 PM, eugene wrote:

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here:
Issues · elastic/elasticsearch · GitHub

Does this feature already exist in the most recent version of ES?
If
yes, which facet should I use? I looked at terms and histogram
facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your
knowledge.

Thank you,
Eugene.- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -

jason · March 17, 2011, 10:26pm

Great, Ivan. It worked for me and I can import termsStats facet into
my project!

I have another problem. What should I use for value_field in
terms_stats facet if none of my fields are numeric?

Thanks,
Eugene.

On Mar 17, 2:21 pm, Ivan Brusic i...@brusic.com wrote:

There is a version in the Sonatype snapshot repo:

https://oss.sonatype.org/content/repositories/snapshots/org/elasticse...

On Thu, Mar 17, 2011 at 5:18 PM, eugene efur...@gmail.com wrote:

Than you, Shay. I built a new version of elasticsearch, and it
contains updated libraries. Is the version 0.16.0 already in maven
repository? I could probably change the version of my elasticsearch
artifact and that would easily work. Please let me know if there is
another workaround, since I am only using maven and want to avoid
adding individual jar files to my project.

On Mar 17, 11:33 am, Shay Banon shay.ba...@elasticsearch.com wrote:

You will need to build elasticsearch from master. Once downloaded /
git-cloned, you can run "gradlew release" from the root, and the
distribution will eb under build/distributions.

Elasticsearch Platform — Find real-time answers at scale | Elastic

On Thursday, March 17, 2011 at 8:30 PM, eugene wrote:

Oh, I understand why I am getting this error. Because I need to
download an update of Elasticsearch with termsstats package. Shay,
could you tell me how I can do it from this page:

Search: Terms Stats Facet, closes #705. · elastic/elasticsearch@352cb74 · GitHub.
..

Thank you,
Eugene.

On Mar 17, 10:21 am, eugene efur...@gmail.com wrote:

I run the following query:

{
"query" : {
"match_all": {}
},
"filter" : {
"range" : {
"timestamp" : {
"from" : "2011-01-01",
"to" : "2011-02-02",
"include_lower" : true,
"include_upper" : false
}
}
},
"facets" : {
"url_count_stats" : {
"terms_stats" : {
"key_field" : "url",
"value_field" : "count"
}
}
}

}

and I am getting the exception:

{"error":"SearchPhaseExecutionException[Failed to execute phase
[query], total f
ailure; shardFailures {[B2ehMgT2TVeGgJtOVZLQJw][test][0]:
SearchParseException[[
test][0]: query[ConstantScore(:)],from[-1],size[-1]: Parse Failure
[Failed to
parse source [{ \t"query" : { \t\t"match_all": {}\t },\t"filter
" : {\t\t
"range" : { \t\t\t"timestamp" : { \t\t\t\t"from" :
"2011-01-01", \t\t\t
\t"to" : "2011-02-02", \t\t\t\t"include_lower" : true, \t\t\t\t
"include
_upper" : false \t\t\t}\t\t} \t},\t"facets" :
{\t\t"url_count_stats
" : {\t
t\t"terms_stats" : {\t\t\t\t"key_field" : "url",\t\t\t\t
"value_field" :
"count"\t\t\t}\t\t}\t}}]]]; nested: SearchParseException[[test][0]:
query[Cons
tantScore(:)],from[-1],size[-1]: Parse Failure [No facet type found
for [terms
_stats]]]; }{[B2ehMgT2TVeGgJtOVZLQJw][test][1]:
SearchParseException[[test][1]:

On Mar 17, 2:31 am, Shay Banon shay.ba...@elasticsearch.com wrote:

Terms stats might be what you are after, its in master:
Issues · elastic/elasticsearch · GitHub.

On Wednesday, March 16, 2011 at 10:00 PM, eugene wrote:

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here:
Issues · elastic/elasticsearch · GitHub

Does this feature already exist in the most recent version of ES?
If
yes, which facet should I use? I looked at terms and histogram
facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your
knowledge.

Thank you,
Eugene.- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -

Lukas_Vlcek1 · March 19, 2011, 6:16am

You can also use 'gradlew release install' to build and install
elasticsearch SNAPSHOT into your local mvn repo. Then you can use it for
your local development. Note that gradle should be able to delegate to mvn
goals so I think whatever you can do with mvn you should be able to do with
gradle too.

Regards,
Lukas
Dne 17.3.2011 22:18 "eugene" efurman@gmail.com napsal(a):

Than you, Shay. I built a new version of elasticsearch, and it
contains updated libraries. Is the version 0.16.0 already in maven
repository? I could probably change the version of my elasticsearch
artifact and that would easily work. Please let me know if there is
another workaround, since I am only using maven and want to avoid
adding individual jar files to my project.

On Mar 17, 11:33 am, Shay Banon shay.ba...@elasticsearch.com wrote:

You will need to build elasticsearch from master. Once downloaded /
git-cloned, you can run "gradlew release" from the root, and the
distribution will eb under build/distributions.

Elasticsearch Platform — Find real-time answers at scale | Elastic

On Thursday, March 17, 2011 at 8:30 PM, eugene wrote:

Oh, I understand why I am getting this error. Because I need to
download an update of Elasticsearch with termsstats package. Shay,
could you tell me how I can do it from this page:

Search: Terms Stats Facet, closes #705. · elastic/elasticsearch@352cb74 · GitHub...

Thank you,
Eugene.

On Mar 17, 10:21 am, eugene efur...@gmail.com wrote:

I run the following query:

{
"query" : {
"match_all": {}
},
"filter" : {
"range" : {
"timestamp" : {
"from" : "2011-01-01",
"to" : "2011-02-02",
"include_lower" : true,
"include_upper" : false
}
}
},
"facets" : {
"url_count_stats" : {
"terms_stats" : {
"key_field" : "url",
"value_field" : "count"
}
}
}

}

and I am getting the exception:

{"error":"SearchPhaseExecutionException[Failed to execute phase
[query], total f
ailure; shardFailures {[B2ehMgT2TVeGgJtOVZLQJw][test][0]:
SearchParseException[[
test][0]: query[ConstantScore(:)],from[-1],size[-1]: Parse Failure
[Failed to
parse source [{ \t"query" : { \t\t"match_all": {}\t },\t"filter
" : {\t\t
"range" : { \t\t\t"timestamp" : { \t\t\t\t"from" :
"2011-01-01", \t\t\t
\t"to" : "2011-02-02", \t\t\t\t"include_lower" : true, \t\t\t\t
"include
_upper" : false \t\t\t}\t\t} \t},\t"facets" :
{\t\t"url_count_stats
" : {\t
t\t"terms_stats" : {\t\t\t\t"key_field" : "url",\t\t\t\t
"value_field" :
"count"\t\t\t}\t\t}\t}}]]]; nested: SearchParseException[[test][0]:
query[Cons
tantScore(:)],from[-1],size[-1]: Parse Failure [No facet type found
for [terms
_stats]]]; }{[B2ehMgT2TVeGgJtOVZLQJw][test][1]:
SearchParseException[[test][1]:

On Mar 17, 2:31 am, Shay Banon shay.ba...@elasticsearch.com wrote:

Terms stats might be what you are after, its in master:
Issues · elastic/elasticsearch · GitHub.

On Wednesday, March 16, 2011 at 10:00 PM, eugene wrote:

Hi,

I would like to write a query in ES that shows counts for each
person's name, equivalent to the following SQL query:

select name, count() from people group by name;

I found a similar request here:
Issues · elastic/elasticsearch · GitHub

Does this feature already exist in the most recent version of ES?
If
yes, which facet should I use? I looked at terms and histogram
facets,
and they don't seem to be appropriate.

If anyone has implemented this before, please share your
knowledge.

Thank you,
Eugene.- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -

Topic		Replies	Views
Grouping by non-numeric fields Elasticsearch	1	300	July 6, 2017
Can elasticsearch do GROUP BY multi fields and ORDER BY count? Elasticsearch	3	26361	May 9, 2019
Returning count on field matches (on a query across all fields) Elasticsearch	3	1786	July 6, 2017
How to get the data with concept in SQL "Group by" Elasticsearch	2	1009	May 18, 2021
Can elasticsearch do GROUP BY and ORDER BY count? Elasticsearch	6	44299	December 6, 2016

Grouping by non-numeric field

Related Topics