# Terms aggregation with ICU multi-field and arrays

**URL:** <https://discuss.elastic.co/t/terms-aggregation-with-icu-multi-field-and-arrays/297899>\
**Category:** Elasticsearch\
**Tags:** runtime-fields\
**Created:** [February 22, 2022, 1:03pm UTC](https://discuss.elastic.co/t/terms-aggregation-with-icu-multi-field-and-arrays/297899 "2022-02-22T13:03:32Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mhugo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mhugo/32/99205_2.png) [@mhugo](https://discuss.elastic.co/u/mhugo)\
**Post date:** [February 22, 2022, 1:03pm UTC](https://discuss.elastic.co/t/terms-aggregation-with-icu-multi-field-and-arrays/297899/1 "2022-02-22T13:03:32Z")

</div>

Hi.  
I struggle to sort buckets of a terms aggregation based on ICU collation to respect the ordering of accented words. And especially when some the text field to sort is indexed as arrays.

I started with a simple term aggregation on a text field. Of course such fields cannot be used in aggregations, so I added a keyword as "sub-field" to the mapping. I also added the collation key from ICU to be able to correctly sort texts. I ended up with something like:

```auto
PUT /test_icu
{
    "mappings": {
        "properties": {
            "t": {
                "type": "text",
                "fields": {
                    "sort": {
                        "type": "icu_collation_keyword",
                        "index": false // I had to add this to get non emtpy results
                    }
                }
            }
        }
    }
}

```

Then adding some simple-valued documents with two simple strings "x" and "î" (i with circumflex accent which should be ordered before x)

```auto
POST /test_icu/_doc/1
{
    "t": "x"
}

#
POST /test_icu/_doc/2
{
    "t": "î"
}

```

Now the aggregation query:

```auto
GET /test_icu/_search
{
    "aggs": {
        "tt": {
            "terms": {
                "field": "t.sort"
            }
        }
    },
    "size": 0
}

```

... gives bucket names that are not human readable (collation keys):

```auto
{
"took":4,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0}
"hits":{"total":{"value":2,"relation":"eq"},"max_score":null,"hits":[]}
"aggregations": {
  "tt":{
    "doc_count_error_upper_bound":0,
    "sum_other_doc_count":0,
    "buckets":[
        {"key":"ᴀ兣䀠怀","doc_count":1},
        {"key":"Ⰰ䅀₠\0\0","doc_count":1}]}}}

```

In order to get the corresponding original string from the icu collation key, I added a sub-aggregation on a "raw" (keyword) sub field (new mapping first, then the query)

```auto
PUT /test_icu
{
    "mappings": {
        "properties": {
            "t": {
                "type": "text",
                "fields": {
                    "sort": {
                        "type": "icu_collation_keyword",
                        "index": false
                    },
                    "raw": {
                        "type": "keyword"
                    }
                }
            }
        }
    }
}

```

```auto
GET /test_icu/_search
{
    "aggs": {
        "tt": {
            "aggs": {
                "traw": {
                    "terms": {
                        "field": "t.raw"
                    }
                }
            },
            "terms": {
                "field": "t.sort"
            }
        }
    },
    "size": 0
}

```

This gives nice results where buckets have a sub bucket key made of the original string (I've replaced non printable characters with "\<non\_printable\>"):

```auto
{
    "aggregations": {
        "tt": {
            "buckets": [
                {
                    "traw": {
                        "buckets": [
                            {
                                "doc_count": 1,
                                "key": "î"
                            }
                        ],
                        "sum_other_doc_count": 0,
                        "doc_count_error_upper_bound": 0
                    },
                    "doc_count": 1,
                    "key": "<non_printable>"
                },
                {
                    "traw": {
                        "buckets": [
                            {
                                "doc_count": 1,
                                "key": "x"
                            }
                        ],
                        "sum_other_doc_count": 0,
                        "doc_count_error_upper_bound": 0
                    },
                    "doc_count": 1,
                    "key": "<non_printable>"
                }
            ],
            "sum_other_doc_count": 0,
            "doc_count_error_upper_bound": 0
        }
    },
    "hits": {
        "hits": [],
        "max_score": null,
        "total": {
            "relation": "eq",
            "value": 2
        }
    },
    "_shards": {
        "failed": 0,
        "skipped": 0,
        "successful": 1,
        "total": 1
    },
    "timed_out": false,
    "took": 2
}

```

Now, what If a new document has a mutli value ? (array):

```auto
POST /test_icu/_doc/3
{
    "t": ["à", "z"]
}

```

It will result in multiple sub buckets for each bucket where it becomes impossible to know which sub bucket key corresponds to the parent bucket key ...

```auto
{
    "aggregations": {
        "tt": {
            "buckets": [
                {
                    "traw": {
                        "buckets": [
                            {
                                "doc_count": 1,
                                "key": "z"
                            },
                            {
                                "doc_count": 1,
                                "key": "à"
                            }
                        ],
                        "sum_other_doc_count": 0,
                        "doc_count_error_upper_bound": 0
                    },
                    "doc_count": 1,
                    "key": "<non_printable1>"
                },
                {
                    "traw": {
                        "buckets": [
                            {
                                "doc_count": 1,
                                "key": "z"
                            },
                            {
                                "doc_count": 1,
                                "key": "à"
                            }
                        ],
                        "sum_other_doc_count": 0,
                        "doc_count_error_upper_bound": 0
                    },
                    "doc_count": 1,
                    "key": "<non_printable2>"
                }
            ],
            "sum_other_doc_count": 0,
            "doc_count_error_upper_bound": 0
        }
    },
    "hits": {
        "hits": [],
        "max_score": null,
        "total": {
            "relation": "eq",
            "value": 1
        }
    },
    "_shards": {
        "failed": 0,
        "skipped": 0,
        "successful": 1,
        "total": 1
    },
    "timed_out": false,
    "took": 852
}

```

I did not find a proper solution to this.  
I've tried a hack where the aggregation is done on the concatenation of the collation key and the raw string, thanks to a script:

```auto
GET /test_icu/_search
{
    "runtime_mappings": {
        "myt": {
            "type": "keyword",
            "script": "for (int i=0;i<doc['t.sort'].size();i++){emit(doc['t.sort'][i] + '||' + doc['t.raw'][i]);}"
        }
    },
    "size": 0,
    "aggs": {
        "tt": {
            "terms": {
                "field": "myt"
            }
        }
    }
}

```

... but this does not work as I would expect, keys are not correctly ordered. It seems there is no guarantee that `doc['t.sort'][i]` correspond to `doc['t.raw'][i]`. The two arrays may be ordered differently ...

Is there a way to address the need of a language-aware sorting of bucket keys ?

Alternatively, is there a way to fix access to the different arrays of a multifield so that we get elements in a fixed order ?

Thanks in advance for your help.  
Hugo

---

<div class="post-metadata">

**Author:** ![mhugo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mhugo/32/99205_2.png) [@mhugo](https://discuss.elastic.co/u/mhugo)\
**Post date:** [February 22, 2022, 1:07pm UTC](https://discuss.elastic.co/t/terms-aggregation-with-icu-multi-field-and-arrays/297899/2 "2022-02-22T13:07:12Z")

</div>

Sorry, duplicate of [ICU sorting of terms aggregation with multi-valued fields](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886). This one can be closed

---

<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:** [March 22, 2022, 1:08pm UTC](https://discuss.elastic.co/t/terms-aggregation-with-icu-multi-field-and-arrays/297899/3 "2022-03-22T13:08:04Z")

</div>

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