# ICU sorting of terms aggregation with multi-valued fields

**URL:** <https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886>\
**Category:** Elasticsearch\
**Tags:** runtime-fields\
**Created:** [February 22, 2022, 11:29am UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886 "2022-02-22T11:29:07Z")\
**Posts on this page:** 8\
**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, 11:29am UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886/1 "2022-02-22T11:29:07Z")

</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 the text field to sort is indexed with 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 single-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
}

```

So far, so good.  
Now, what If a new document has a mutli value ? (array):

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

```

... now the query gives multiple sub buckets and we cannot tell which original value correspond to which collation key (omitting the first two documents for clarity):

```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": 3
}

```

I cannot find a proper solution to this.

I've tried to aggregate and sort on the concatenation of the icu collation and the original string, with something like this:

```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"
            }
        }
    }
}

```

However, results are not what I would expect (they are not sorted correctly). I think this is because there is no guarantee that `doc['t.sort'][i]` corresponds to `doc['t.raw'][i]`, both arrays may be in different arbitrary orders ...

Is there any way to address this issue ?

Thanks in advance for your help.  
Hugo

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 22, 2022, 5:10pm UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886/2 "2022-02-22T17:10:55Z")

</div>

If you want to sort terms in terms aggregation, you have to specify "[order](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-order)" parameter.

How about

```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",
                "order": { "_key": "asc" }
            }
        }
    }
}

```

---

<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 23, 2022, 3:28pm UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886/3 "2022-02-23T15:28:50Z")

</div>

Sure, thanks.  
But the problem comes from the fact that array values in a multi field cannot be accessed in a consistent order in scripts.  
Let's simplify a bit to illustrate. With this simple query (I've added encodeBase64 so that we can print collation keys):

```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].encodeBase64() + '||' + doc['t.raw'][i]);}"
        }
    },
    "_source": {},
    "fields": [{"field": "myt"}]
}

```

... I get the following result in "fields":

```auto
                "fields": {
                    "myt": [
                        "KgFFwooBBgA=||z",
                        "XAEFAQUA||à"
                    ]
                },

```

... which would mean "à" has an collation key of "XAEFAQUA" (in base64). However, this is wrong, the collation key of "à" is "KgFFwooBBgA=" (confirmed manually with pyICU). So my conclusion is that this concatenation hack cannot work.  
So I cannot find a way to sort term aggregations respecting the language ordering when array values are used with this technique.

The only alternative would be to do this concatenation at index time by the client ... or patch the analysis-icu aggregation somehow ...

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 23, 2022, 3:46pm UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886/4 "2022-02-23T15:46:25Z")

</div>

What will happen if you use `params['_source']['t.sort']` rather than `doc['t.sort']`?  
This could raise performance issue, but the order should be strictly the same as the source you created.

> **[Accessing document fields and special variables | Elasticsearch Guide \[master\]...](https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-fields.html)**

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 23, 2022, 3:49pm UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886/5 "2022-02-23T15:49:15Z")

</div>

Hm, it will not work because there could not be the field of `t.sort` nor `t.raw` field in the \_source...

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 23, 2022, 3:54pm UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886/6 "2022-02-23T15:54:44Z")

</div>

Anyway, why you need bucket sorting?

---

<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 24, 2022, 3:35pm UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886/7 "2022-02-24T15:35:32Z")

</div>

Thanks for your suggestion, but yes, it does not work, since `t.sort` and `t.raw` are not part of `_source`.

> Anyway, why you need bucket sorting?

Well I just need to sort results of a term aggregations. But my use case involves an order that must respect the language aware order of texts. And on top of that, it should work with array values.

I am considering writing a plugin just for this use case, but it seems a bit overkill to me ...

---

<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 24, 2022, 3:36pm UTC](https://discuss.elastic.co/t/icu-sorting-of-terms-aggregation-with-multi-valued-fields/297886/8 "2022-03-24T15:36:05Z")

</div>

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