Grouping by non-numeric field

Elastic Stack Elasticsearch
1

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.

2

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.

3

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 -
4

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 -
5

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 -
6

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 -
7

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 -
8

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 -
9

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 -