# Is case-insensitive sort on keyword aggregation. ES 7.7.0

**URL:** <https://discuss.elastic.co/t/is-case-insensitive-sort-on-keyword-aggregation-es-7-7-0/268198>\
**Category:** Elasticsearch\
**Created:** [March 24, 2021, 11:12am UTC](https://discuss.elastic.co/t/is-case-insensitive-sort-on-keyword-aggregation-es-7-7-0/268198 "2021-03-24T11:12:11Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Cular](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cular/32/85972_2.png) [@Cular](https://discuss.elastic.co/u/Cular)\
**Post date:** [March 24, 2021, 11:12am UTC](https://discuss.elastic.co/t/is-case-insensitive-sort-on-keyword-aggregation-es-7-7-0/268198/1 "2021-03-24T11:12:11Z")

</div>

I have similar case like [here](https://discuss.elastic.co/t/keyword-type-aggregation-case-insensitive/82682) and [here2](https://discuss.elastic.co/t/is-case-insensitive-sort-on-keyword-aggregation-possible-es-6-4/153003/4).

I am trying to achive alphabetical sorting (case insensitive) with original value from aggregation.

Simplified example mapping:

```auto
PUT test_data
{
    "mappings": {
        "properties": {
            "id": {
                "type": "keyword"
            },
            "name@String": {
                "properties": {
                    "values": {
                        "properties": {
                            "defaultValue": {
                                "type": "text",
                                "fields": {
                                    "keyword": {
                                        "type": "keyword",
                                        "ignore_above": 256
                                    },
                                    "sortword": {
                                        "type": "keyword",
                                        "ignore_above": 256,
                                        "normalizer": "case_insensitive"
                                    }
                                }
                            },
                            "translations": {
                                "properties": {
                                    "0": {
                                        "type": "text",
                                        "fields": {
                                            "keyword": {
                                                "type": "keyword",
                                                "ignore_above": 256
                                            },
                                            "sortword": {
                                                "type": "keyword",
                                                "ignore_above": 256,
                                                "normalizer": "case_insensitive"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    "settings": {
        "analysis": {
            "normalizer": {
                "case_insensitive": {
                    "filter": [
                        "lowercase"
                    ],
                    "type": "custom"
                }
            }
        }
    }
}

```

My test data:

```auto
POST test_data/_doc/
{
    "name@String": {
        "values": {
            "defaultValue": "CCC",
            "translations": {
                "0": "CCC"
            }
        }
    }
}

POST test_data/_doc/
{
    "name@String": {
        "values": {
            "defaultValue": "bbb",
            "translations": {
                "0": "bbb"
            }
        }
    }
}

POST test_data/_doc/
{
    "name@String": {
        "values": {
            "defaultValue": "BBB",
            "translations": {
                "0": "BBB"
            }
        }
    }
}

POST test_data/_doc/
{
    "name@String": {
        "values": {
            "defaultValue": "aaa",
            "translations": {
                "0": "aaa"
            }
        }
    }
}

POST test_data/_doc/
{
    "name@String": {
        "values": {
            "defaultValue": "AAA",
            "translations": {
                "0": "AAA"
            }
        }
    }
}

```

[Terms aggregation Order paragraph](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-order) says: Ordering the buckets **alphabetically** by their terms in an ascending manner.  
Here is my aggregation\_1:

```auto
POST test_data/_search
{
    "query": {
        "match_all": {}
    },
    "aggs": {
        "names": {
            "terms": {
                "field": "name@String.values.translations.0.keyword",
                "order": {
                    "_key": "asc"
                }
            }
        }
    },
    "size": 0
}

```

Result:

```auto
AAA
BBB
CCC
aaa
bbb

```

Expected (order between cases does not matter):

```auto
AAA
aaa
bbb
BBB
CCC

```

Here is my aggregation\_2:

```auto
POST test_data/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "names": {
      "terms": {
        "field": "name@String.values.translations.0.sortword",
        "order": { "_key": "asc" }
      }
    }
  }, 
  "size": 0
}

```

Result:

```auto
aaa,
bbb,
ccc

```

I have tried to do something with pipelines, like sort by **sortword** and term **keyword** , but have no success.

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [March 25, 2021, 1:35am UTC](https://discuss.elastic.co/t/is-case-insensitive-sort-on-keyword-aggregation-es-7-7-0/268198/2 "2021-03-25T01:35:04Z")

</div>

If you want to get all the results, why are you not using a search (instead of the aggregation) and sort on the lowercased field?

---

<div class="post-metadata">

**Author:** ![Cular](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cular/32/85972_2.png) [@Cular](https://discuss.elastic.co/u/Cular)\
**Post date:** [March 25, 2021, 6:12am UTC](https://discuss.elastic.co/t/is-case-insensitive-sort-on-keyword-aggregation-es-7-7-0/268198/3 "2021-03-25T06:12:57Z")

</div>

Hello, xeraa! Thanks for reply. Indeed I have much more complicated model and I'm using search also, when I have to show all documents. And sorting by normalized keyword works perfectly! But I must show aggregations by specific field also. That's why I have this short example.

My main point is insensitive sorting for search and aggregation with saving case of text. Any chance achieve that?

UPD1:

```auto
POST test_data/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "sortNames": {
      "terms": {
        "field": "name@String.values.translations.0.sortword",
        "order": { "_key": "asc" }
      },
      "aggs": {
        "names": {
          "terms": {
            "field": "name@String.values.translations.0.keyword"
          }
        }
      }
    }
  }, 
  "size": 0
}

```

Aggregation above looks like temporary solution with reading values from subaggregation

```auto
"sortNames" : {
    "doc_count_error_upper_bound" : 0,
    "sum_other_doc_count" : 0,
    "buckets" : [
    {
        "key" : "aaa",
        "doc_count" : 2,
        "names" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
            {
            "key" : "AAA",
            "doc_count" : 1
            },
            {
            "key" : "aaa",
            "doc_count" : 1
            }
        ]
        }
    },
    {
        "key" : "bbb",
        "doc_count" : 3,
        "names" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
            {
            "key" : "BBB",
            "doc_count" : 1
            },
            {
            "key" : "BBb",
            "doc_count" : 1
            },
            {
            "key" : "bbb",
            "doc_count" : 1
            }
        ]
        }
    },
    {
        "key" : "ccc",
        "doc_count" : 1,
        "names" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
            {
            "key" : "CCC",
            "doc_count" : 1
            }
        ]
        }
    }
    ]
}

```

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [March 26, 2021, 4:44pm UTC](https://discuss.elastic.co/t/is-case-insensitive-sort-on-keyword-aggregation-es-7-7-0/268198/4 "2021-03-26T16:44:49Z")

</div>

> [@Cular](#):
>
> But I must show aggregations by specific field also.

Not sure that is clear enough to fully understand the tradeoffs here, but maybe `collapse` is what you're after if you want all the individual results / documents? [Collapse search results | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/collapse-search-results.html)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 23, 2021, 4:45pm UTC](https://discuss.elastic.co/t/is-case-insensitive-sort-on-keyword-aggregation-es-7-7-0/268198/5 "2021-04-23T16:45:50Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.