# Terms Aggregation lost result **URL:** \ **Category:** Kibana\ **Created:** [August 4, 2016, 2:28am UTC](https://discuss.elastic.co/t/terms-aggregation-lost-result/57168 "2016-08-04T02:28:16Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![abcfy2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abcfy2/32/453_2.png) [@abcfy2](https://discuss.elastic.co/u/abcfy2)\ **Post date:** [August 4, 2016, 2:28am UTC](https://discuss.elastic.co/t/terms-aggregation-lost-result/57168/1 "2016-08-04T02:28:16Z")
Hi all. I want to aggregate the log of postgresql slow query. Here is my log in kibana: ![](https://us1.discourse-cdn.com/elastic/original/2X/6/63f668469932a2d765b21fcdda3aec6fdd77b82f.png) You can see there are 13 hits. I want to execute as this SQL: ```sql SELECT slow_sql.raw, avg(duration), count(1) FROM 'logstash-*' GROUP BY slow_sql.raw; ``` Here is my table: ![](https://us1.discourse-cdn.com/elastic/original/2X/b/be9f9f5f0cb39327292b6c1b4fe03ef9b589c871.png) Only one result. What's wrong with my operation? ---
**Author:** ![shaunak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shaunak/32/6643_2.png) [@shaunak](https://discuss.elastic.co/u/shaunak)\ **Post date:** [August 4, 2016, 4:22am UTC](https://discuss.elastic.co/t/terms-aggregation-lost-result/57168/2 "2016-08-04T04:22:21Z")
Everything looks right at first glance. Can you paste the entire output of the Elasticsearch response body here please? Thanks! ---
**Author:** ![abcfy2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abcfy2/32/453_2.png) [@abcfy2](https://discuss.elastic.co/u/abcfy2)\ **Post date:** [August 4, 2016, 5:13am UTC](https://discuss.elastic.co/t/terms-aggregation-lost-result/57168/3 "2016-08-04T05:13:20Z")
Request: ```json { "size": 0, "aggs": { "2": { "terms": { "field": "slow_sql.raw", "size": 5, "order": { "_count": "desc" } }, "aggs": { "3": { "avg": { "field": "duration" } } } } }, "highlight": { "pre_tags": [ "@kibana-highlighted-field@" ], "post_tags": [ "@/kibana-highlighted-field@" ], "fields": { "*": {} }, "require_field_match": false, "fragment_size": 2147483647 }, "query": { "filtered": { "query": { "query_string": { "query": "*", "analyze_wildcard": true } }, "filter": { "bool": { "must": [ { "query": { "match": { "type": { "query": "postgresql", "type": "phrase" } } }, "$state": { "store": "appState" } }, { "query": { "match": { "tags": { "query": "slow_query", "type": "phrase" } } }, "$state": { "store": "appState" } }, { "range": { "@timestamp": { "gte": 1470286624761, "lte": 1470287524761, "format": "epoch_millis" } } } ], "must_not": [] } } } } } ``` Response: ```json { "took": 1039, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 14, "max_score": 0, "hits": [] }, "aggregations": { "2": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "3": { "value": 566.851 }, "key": "statement: BEGIN;select distinct(data_id) from sym_data_event where data_id >=102711 and data_id <= 50102710 order by data_id asc ", "doc_count": 1 }, { "3": { "value": 1320.955 }, "key": "statement: SELECT DISTINCT(equipment_id) FROM moni_equipment_item;", "doc_count": 1 } ] } } } ``` ---
**Author:** ![abcfy2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abcfy2/32/453_2.png) [@abcfy2](https://discuss.elastic.co/u/abcfy2)\ **Post date:** [August 4, 2016, 8:13am UTC](https://discuss.elastic.co/t/terms-aggregation-lost-result/57168/4 "2016-08-04T08:13:33Z")
It seems that the `slow_sql.raw` is too long. I find the logstash default es template contains this config: ```json "string_fields" : { "match" : "*", "match_mapping_type" : "string", "mapping" : { "type" : "string", "index" : "analyzed", "omit_norms" : true, "fielddata" : { "format" : "disabled" }, "fields" : { "raw" : {"type": "string", "index" : "not_analyzed", "ignore_above" : 256} } } } } ``` Maybe if the `slow_sql.raw` is more than `256` bytes will be ignored by `GROUP BY`? ---
**Author:** ![shaunak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shaunak/32/6643_2.png) [@shaunak](https://discuss.elastic.co/u/shaunak)\ **Post date:** [August 9, 2016, 5:27pm UTC](https://discuss.elastic.co/t/terms-aggregation-lost-result/57168/5 "2016-08-09T17:27:29Z")
Hi, I think you are right about the `ignore_above`. According to [its documentation](https://www.elastic.co/guide/en/elasticsearch/reference/current/ignore-above.html) the field will not be indexed at all if its length is greater than the value specified in `ignore_above`. I was able to run a small test to prove this: ``` PUT my_index { "mappings": { "my_type": { "properties": { "message": { "type": "text", "fields": { "raw": { "type": "keyword", "ignore_above": 20 } } } } } } } POST my_index/my_type { "message": "a short message" } POST my_index/my_type { "message": "a very long message that might not be indexed because it is too long" } POST my_index/my_type/_search POST my_index/my_type/_search { "size": 0, "aggs": { "message": { "terms": { "field": "message.raw" } } } } ``` So it seems like your solution would be to increase the value of `ignore_above` in the Logstash Elasticsearch template OR perhaps even remove that option altogether. Then, any _new_ messages you index using Logstash should be indexed completely as raw fields. For messages you have already indexed, you will need to reindex them. ---
**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:41pm UTC](https://discuss.elastic.co/t/terms-aggregation-lost-result/57168/6 "2017-07-06T13:41:32Z")
Closed on July 6, 2017, 1:41pm UTC