# Query and Aggregation result doesn't match
**URL:** https://discuss.elastic.co/t/query-and-aggregation-result-doesnt-match/350362
**Category:** Kibana
**Tags:** painless
**Created:** [January 4, 2024, 7:07am UTC](https://discuss.elastic.co/t/query-and-aggregation-result-doesnt-match/350362 "2024-01-04T07:07:00Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@shufan](https://discuss.elastic.co/u/shufan)
#### Post date: [January 4, 2024, 7:07am UTC](https://discuss.elastic.co/t/query-and-aggregation-result-doesnt-match/350362/1 "2024-01-04T07:07:00Z")
Hi team, got a strange issue when doing query and aggregation. Here is my script
```auto
GET userindex/_search
{ "_source":["response4"],
"query":{
"bool": {
"filter": [
{"script": {
"script": {
"source": """
return doc['category.keyword'].value == 'StudioUI' && doc['id.keyword'].value == '8a07b6be-c9c9-4002-b989-a4ca67ef51d2'
""",
"lang": "painless"
}
}
}
]
}},
"aggs": {
"FieldAff": {
"terms": {
"field": "response4.keyword",
"size": 10000,
"show_term_doc_count_error": true,
"missing": "Missing"
}
}
}
}
```
the result I got is
```auto
{
"took" : 214,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 0.0,
"hits" : [
{
"_index" : "userindex",
"_type" : "_doc",
"_id" : "8a07b6be-c9c9-4002-b989-a4ca67ef51d2",
"_score" : 0.0,
"_routing" : "8a07b6be-c9c9-4002-b989-a4ca67ef51d2",
"_source" : {
"response4" : """Something went wrong
We encountered an error while preparing to depldpoint. You can get more details below.
An error occurred (Resed) when calling the Creaoint operation: The aunt-leel service limit 'ml.p3.2xle for endpage' is 0 Instances, with current utilization of 0 Instances and a request delta of 1 Instances. Pleice Quotas to request an increase for thota. If Available, contapport to reque"""
}
}
]
},
"aggregations" : {
"FieldAff" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "Missing",
"doc_count" : 1,
"doc_count_error_upper_bound" : 0
}
]
}
}
}
```
From the query result, we can see there is “response4” field value, but in the aggregation result, this is marked as “Missing”. Could anyone help explain this? thanks
---
### Author:  [@lukas](https://discuss.elastic.co/u/lukas)
#### Post date: [January 4, 2024, 10:30pm UTC](https://discuss.elastic.co/t/query-and-aggregation-result-doesnt-match/350362/2 "2024-01-04T22:30:24Z")
It looks like there is no `response4.keyword` value indexed in the one document that was returned in your query, so it shows up in the "missing" key.
Can you explain a little more about what you're trying to accomplish here? Doing a terms aggregation on a text value probably isn't very helpful, which is what your query is attempting to accomplish.
---
### Author:  [@shufan](https://discuss.elastic.co/u/shufan)
#### Post date: [January 4, 2024, 11:15pm UTC](https://discuss.elastic.co/t/query-and-aggregation-result-doesnt-match/350362/3 "2024-01-04T23:15:24Z")
Thank you for the response. I try to find the max length of response4. So think of one approach is doing stats aggregation on [response4.keyword].value.length(), as in our mapping [response4] is not aggregable and only [response4.keyword] is aggregable. Both fields hold the same value. When running script, no error returned and I got the max, min, sum... values for field. But latter I noticed some of the [response4.keyword] values are not included in the aggregation result. After checking I found both response4 and response4.keyword fields are populated with the same value for those docs. But those records are with longer string [response4.keyword] values. Also used the script I posted above, I confirmed no aggregation can be done on those records. So kind of confused why not able to include those records.
---
### Author:  [@lukas](https://discuss.elastic.co/u/lukas)
#### Post date: [January 5, 2024, 5:05pm UTC](https://discuss.elastic.co/t/query-and-aggregation-result-doesnt-match/350362/4 "2024-01-05T17:05:46Z")
If you check the mapping for this field, it's likely you'll see something like the following:
```auto
"response4": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
```
What this means is that if the value is longer than the `ignore_above` length, the keyword version of the field will not be indexed. You can find more information about this property here: [ignore\_above | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/8.11/ignore-above.html)
This is likely why you're not able to use the longer values in aggregations.
Instead of using the keyword, you might want to just retrieve the value from `_source` in your script:
```auto
emit(params['_source'].response4.length())
```
Could you give that a try and see if it works for you?
---
### Author:  [@shufan](https://discuss.elastic.co/u/shufan)
#### Post date: [January 5, 2024, 7:07pm UTC](https://discuss.elastic.co/t/query-and-aggregation-result-doesnt-match/350362/5 "2024-01-05T19:07:59Z")
Thank you so much. Checked our mapping, it is with ignore\_above: 256. Guess here is to suggest using runtime field. As our ES version is \< 7.12, this feature is not available. But following your suggestion with idea doing max on `params['_source'][response4].length()` solve the problem.
```auto
GET userIndex/_search
{ "_source": ["response4"],
"query": {
"exists": {
"field": "response4"
}
},
"aggs": {
"maxLen": {
"max": {
"script" : "if ( ! (params['_source']['response3'] instanceof List)) {return params['_source']['response3'].length();} else {return 1;}"
}
}
}
}
```
Appreciate your help.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [February 2, 2024, 7:08pm UTC](https://discuss.elastic.co/t/query-and-aggregation-result-doesnt-match/350362/6 "2024-02-02T19:08:27Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.