How to get only aggregation values from elasticsearch

bagui · May 22, 2014, 11:46am

Hi,

I want to get the average value of MEMORY field from my ES document. Below
is the query I'm using for that. Here I'm getting the aggregation along
with the hits Json also. Is there any way we can get the aggreation result
only. Please suggest.

POST /virtualmachines/_search
{
"query" : {
"filtered" : {
"query" : { "match" : {
"CLOUD_TYPE" : "CLOUDSTACK"
}},
"filter" : {
"range" : { "NODE_CREATE_TIME" : { "from" : "2014-05-22
14:11:35", "to" : "2014-05-22 14:33:35" }}
}
}
},
"aggs" : {
"memory_avg" : { "avg" : { "field" : "MEMORY" } }
}
}

response :
{
"took": 2,
"timed_out": false,
"_shards": {
"total": 3,
"successful": 3,
"failed": 0
},
"hits": {
"total": 1,
"max_score": 1,
"hits": [
{
"_index": "virtualmachines",
"_type": "nodes",
"_id": "102",
"_score": 1,
"_source": {
"NODE_ID": "12235",
"CLOUD_TYPE": "CLOUDSTACK",
"NODE_GROUP_NAME": "JBOSS",
"NODE_CPU": "4GHZ",
"NODE_HOSTNAME": "cloud.aricent.com",
"NODE_NAME": "cloudstack-node1",
"NODE_PRIVATE_IP_ADDRESS": "10.123.124.125",
"NODE_PUBLIC_IP_ADDRESS": "125.31.108.72",
"NODE_INSTANCE_ID": "cloudstack112",
"NODE_STATUS": "ACTIVE",
"NODE_CATEGORY_ID": "13",
"NODE_CREATE_TIME": "2014-05-22 14:23:04",
"CPU_SPEED": "500",
"MEMORY": 512,
"CPU_USED": "0.03%"
}
}
]
},
"aggregations": {
"memory_avg": {
"value": 512
}
}
}

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/a8538769-7ba5-4ad5-b190-5f0b8d25a26b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

emeschitc · May 22, 2014, 1:02pm

On Thu, May 22, 2014 at 1:47 PM, bagui [via Elasticsearch Users] <
ml-node+s115913n4056274h86@n3.nabble.com> wrote:

Hi,

I want to get the average value of MEMORY field from my ES document. Below
is the query I'm using for that. Here I'm getting the aggregation along
with the hits Json also. Is there any way we can get the aggreation result
only. Please suggest.

POST /virtualmachines/_search
{
"query" : {
"filtered" : {
"query" : { "match" : {
"CLOUD_TYPE" : "CLOUDSTACK"
}},
"filter" : {
"range" : { "NODE_CREATE_TIME" : { "from" : "2014-05-22
14:11:35", "to" : "2014-05-22 14:33:35" }}
}
}
},
"aggs" : {
"memory_avg" : { "avg" : { "field" : "MEMORY" } }
}
}

This is how I do it:
Simply add a post_filter with a term query for a non-existent term or a
value which is not possible (e.g. -1 for a positive integer field).
I am sure there are cleaner ways but this works for me

em

emeschitc · May 22, 2014, 1:10pm

Hi
On Thu, May 22, 2014 at 1:47 PM, bagui [via Elasticsearch Users] <
ml-node+s115913n4056274h86@n3.nabble.com> wrote:

Hi,

I want to get the average value of MEMORY field from my ES document. Below
is the query I'm using for that. Here I'm getting the aggregation along
with the hits Json also. Is there any way we can get the aggreation result
only. Please suggest.

POST /virtualmachines/_search
{
"query" : {
"filtered" : {
"query" : { "match" : {
"CLOUD_TYPE" : "CLOUDSTACK"
}},
"filter" : {
"range" : { "NODE_CREATE_TIME" : { "from" : "2014-05-22
14:11:35", "to" : "2014-05-22 14:33:35" }}
}
}
},
"aggs" : {
"memory_avg" : { "avg" : { "field" : "MEMORY" } }
}
}

This is how I do it:
Simply add a post_filter with a term query for a non-existent term or a
value which is not possible (e.g. -1 for a positive integer field).
I am sure there are cleaner ways but this works for me

em

Ivan · May 22, 2014, 5:56pm

You can set the size to 0.

You will still get back the search metadata though.

--
Ivan

On Thu, May 22, 2014 at 4:46 AM, Subhadip Bagui i.bagui@gmail.com wrote:

Hi,

I want to get the average value of MEMORY field from my ES document. Below
is the query I'm using for that. Here I'm getting the aggregation along
with the hits Json also. Is there any way we can get the aggreation result
only. Please suggest.

POST /virtualmachines/_search
{
"query" : {
"filtered" : {
"query" : { "match" : {
"CLOUD_TYPE" : "CLOUDSTACK"
}},
"filter" : {
"range" : { "NODE_CREATE_TIME" : { "from" : "2014-05-22
14:11:35", "to" : "2014-05-22 14:33:35" }}
}
}
},
"aggs" : {
"memory_avg" : { "avg" : { "field" : "MEMORY" } }
}
}

response :
{
"took": 2,
"timed_out": false,
"_shards": {
"total": 3,
"successful": 3,
"failed": 0
},
"hits": {
"total": 1,
"max_score": 1,
"hits": [
{
"_index": "virtualmachines",
"_type": "nodes",
"_id": "102",
"_score": 1,
"_source": {
"NODE_ID": "12235",
"CLOUD_TYPE": "CLOUDSTACK",
"NODE_GROUP_NAME": "JBOSS",
"NODE_CPU": "4GHZ",
"NODE_HOSTNAME": "cloud.aricent.com",
"NODE_NAME": "cloudstack-node1",
"NODE_PRIVATE_IP_ADDRESS": "10.123.124.125",
"NODE_PUBLIC_IP_ADDRESS": "125.31.108.72",
"NODE_INSTANCE_ID": "cloudstack112",
"NODE_STATUS": "ACTIVE",
"NODE_CATEGORY_ID": "13",
"NODE_CREATE_TIME": "2014-05-22 14:23:04",
"CPU_SPEED": "500",
"MEMORY": 512,
"CPU_USED": "0.03%"
}
}
]
},
"aggregations": {
"memory_avg": {
"value": 512
}
}
}

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/a8538769-7ba5-4ad5-b190-5f0b8d25a26b%40googlegroups.com https://groups.google.com/d/msgid/elasticsearch/a8538769-7ba5-4ad5-b190-5f0b8d25a26b%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCfKh_qbSiEJtj_%3DS%3DZ5Q43Lw3V4egRVEdsvz46FvvqPw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

bagui · May 23, 2014, 6:00am

Thanks Ivan for your reply.
I tried with size : 0 but still the metadata is coming. Is there any way
to suppress the metadata.
Can I use java api to do so. Earlier I used the method like below to get
the source response only. Can we do that for aggregation also?

public String searchESIndex(String index, String type) {
Client client = ESClientFactory.getInstance();
SearchRequestBuilder srequest = client.prepareSearch(index)
.setTypes(type).setSearchType(SearchType.QUERY_AND_FETCH)
.setQuery(QueryBuilders.matchQuery("status", "SUSPEND"))
.setSize(1);
SearchResponse sresp = srequest.execute().actionGet();
List<Map<String, Object>> listOfSource = new ArrayList<Map<String,
Object>>();
for (SearchHit hits : sresp.getHits()) {
listOfSource.add(hits.getSource());
}
Gson gson = new Gson();
String jsonSourceList = gson.toJson(listOfSource);
return jsonSourceList;
}

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/47f686b0-e844-410b-955e-6dfb88221569%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Ivan · May 23, 2014, 6:09am

As mentioned before, you will still get the metadata. With the Java API,
you can get the aggregations back directly from the SearchResponse object,
which is a list of Aggregation objects:

https://github.com/elasticsearch/elasticsearch/blob/master/src/main/java/org/elasticsearch/action/search/SearchResponse.java#L103-L105

--
Ivan

On Thu, May 22, 2014 at 11:00 PM, Subhadip Bagui i.bagui@gmail.com wrote:

Thanks Ivan for your reply.
I tried with size : 0 but still the metadata is coming. Is there any
way to suppress the metadata.
Can I use java api to do so. Earlier I used the method like below to get
the source response only. Can we do that for aggregation also?

public String searchESIndex(String index, String type) {
Client client = ESClientFactory.getInstance();
SearchRequestBuilder srequest = client.prepareSearch(index)
.setTypes(type).setSearchType(SearchType.QUERY_AND_FETCH)
.setQuery(QueryBuilders.matchQuery("status", "SUSPEND"))
.setSize(1);
SearchResponse sresp = srequest.execute().actionGet();
List<Map<String, Object>> listOfSource = new ArrayList<Map<String,
Object>>();
for (SearchHit hits : sresp.getHits()) {
listOfSource.add(hits.getSource());
}
Gson gson = new Gson();
String jsonSourceList = gson.toJson(listOfSource);
return jsonSourceList;
}

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/47f686b0-e844-410b-955e-6dfb88221569%40googlegroups.com https://groups.google.com/d/msgid/elasticsearch/47f686b0-e844-410b-955e-6dfb88221569%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQA-Q_s51SuWOpcqPc-ALu_bWVy6ehsMcH59KoGYH9Hyhw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

bagui · May 23, 2014, 7:26am

Thanks a lot Ivan. Will try with Java API as u suggested.

One more doubt. There is a field in my document like "CPU_USED": "0.03%"
Can we do aggregation avg on this field ? I tried but got exception like
below as this was a string field. Can u please suggest.

"reason": "QueryPhaseExecutionException[[virtualmachines][2]:
query[filtered(CLOUD_TYPE:cloudstack)->cache(NODE_CREATE_TIME:[1400767895000
TO 1400769215999])],from[0],size[0]: Query Failed [Failed to execute main
query]]; nested: AggregationExecutionException[Unsupported script value
[0.03]]; "
Query :
POST /virtualmachines/_search
{
"query" : {
"filtered" : {
"query" : { "match" : {
"CLOUD_TYPE" : "CLOUDSTACK"
}},
"filter" : {
"range" : { "NODE_CREATE_TIME" : { "from" : "2014-05-22
14:11:35", "to" : "2014-05-22 14:33:35" }}
}
}
},
"aggs" : {
"avg_grade" : { "avg" : { "script" : "doc['CPU_USED'].value" } }
},
"size": 0
}

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/bd12c67b-9369-4231-90ec-a818a6e9be9a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Ivan · May 23, 2014, 3:43pm

I have yet to use aggregations (still on facets), so I am making
assumptions here. Your field is a string ("0.03%"), so the average cannot
be computed. Since you pass in a script, you can try doing some string
manipulation, and perhaps converting to a numeric field if it is required.

Something like: doc['CPU_USED'].value.replace("%", "") (not tested)

--
Ivan

On Fri, May 23, 2014 at 12:26 AM, Subhadip Bagui i.bagui@gmail.com wrote:

Thanks a lot Ivan. Will try with Java API as u suggested.

One more doubt. There is a field in my document like "CPU_USED": "0.03%"
Can we do aggregation avg on this field ? I tried but got exception like
below as this was a string field. Can u please suggest.

"reason": "QueryPhaseExecutionException[[virtualmachines][2]:
query[filtered(CLOUD_TYPE:cloudstack)->cache(NODE_CREATE_TIME:[1400767895000
TO 1400769215999])],from[0],size[0]: Query Failed [Failed to execute main
query]]; nested: AggregationExecutionException[Unsupported script value
[0.03]]; "
Query :
POST /virtualmachines/_search
{
"query" : {
"filtered" : {
"query" : { "match" : {
"CLOUD_TYPE" : "CLOUDSTACK"
}},
"filter" : {
"range" : { "NODE_CREATE_TIME" : { "from" : "2014-05-22
14:11:35", "to" : "2014-05-22 14:33:35" }}
}
}
},
"aggs" : {
"avg_grade" : { "avg" : { "script" : "doc['CPU_USED'].value" } }
},
"size": 0
}

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/bd12c67b-9369-4231-90ec-a818a6e9be9a%40googlegroups.com https://groups.google.com/d/msgid/elasticsearch/bd12c67b-9369-4231-90ec-a818a6e9be9a%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQAM%2BxOoXFRRs59WdLHYfa0P0RjOnuXZp1a1VPpa_qgjQQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.