# Distinct collect first letters from field

**URL:** <https://discuss.elastic.co/t/distinct-collect-first-letters-from-field/105521>\
**Category:** Elasticsearch\
**Created:** [October 27, 2017, 6:33am UTC](https://discuss.elastic.co/t/distinct-collect-first-letters-from-field/105521 "2017-10-27T06:33:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alexey\_Melnikov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alexey_melnikov/32/23060_2.png) [@Alexey\_Melnikov](https://discuss.elastic.co/u/Alexey_Melnikov)\
**Post date:** [October 27, 2017, 6:33am UTC](https://discuss.elastic.co/t/distinct-collect-first-letters-from-field/105521/1 "2017-10-27T06:33:06Z")

</div>

Good day.

I want to create navigation in index by first letters like in vocabulary.

Ideally I need aggregation which will provide to me a set of all first letters from one field with first document id related to each letter.  
Otherwise will be enough just a set of first letters collected from one field.

But I cannot find aggregation which can do this for me.

Any suggestions will be helpful. Thanks.

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [October 28, 2017, 1:32am UTC](https://discuss.elastic.co/t/distinct-collect-first-letters-from-field/105521/2 "2017-10-28T01:32:55Z")

</div>

You can extract the first letter using script in terms aggregation, but I think the most effective way would be to actually index this first letter as a separate field, using a normalizer. Here is a couple of examples to get you started.

```auto
DELETE test

PUT test
{
  "settings": {
    "analysis": {
      "char_filter": {
        "first_letter_filter": {
          "type": "pattern_replace",
          "pattern": "(.).*",
          "replacement": "$1"
        }
      },
      "normalizer": {
        "first_letter_normilizer": {
          "type": "custom",
          "char_filter": ["first_letter_filter"],
          "filter": [
            "uppercase"
          ]
        }
      }
    }
  },
  "mappings": {
    "doc": {
      "properties": {
        "berry": {
          "type": "keyword",
          "fields": {
            "first": {
              "type": "keyword",
              "normalizer": "first_letter_normilizer"
            }
          }
        }
      }
    }
  }
}

PUT test/doc/_bulk?refresh=true
{"index": {"_id": "1"}}
{"berry": "Blackcurrant"}
{"index": {"_id": "2"}}
{"berry": "Chili pepper"}
{"index": {"_id": "3"}}
{"berry": "Cranberry"}
{"index": {"_id": "4"}}
{"berry": "Eggplant"}
{"index": {"_id": "5"}}
{"berry": "Gooseberry"}
{"index": {"_id": "6"}}
{"berry": "Grape"}
{"index": {"_id": "8"}}
{"berry": "Guava"}
{"index": {"_id": "9"}}
{"berry": "Kiwifruit"}
{"index": {"_id": "10"}}
{"berry": "Lucuma"}
{"index": {"_id": "11"}}
{"berry": "Pomegranate"}
{"index": {"_id": "12"}}
{"berry": "Redcurrant"}
{"index": {"_id": "13"}}
{"berry": "Tomato"}

GET test/doc/_search
{
  "size": 0,
  "aggs": {
    "alphabet": {
      "terms": {
        "field": "berry.first",
        "size": 26,
        "order": {
          "_key": "asc"
        }
      }
    }
  }
}

GET test/doc/_search
{
  "size": 0,
  "aggs": {
    "alphabet": {
      "terms": {
        "field": "berry",
        "size": 26,
        "script" : {
            "source" : "_value.substring(0,1).toUpperCase()",
            "lang" : "painless"
        },
        "order": {
          "_key": "asc"
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Alexey\_Melnikov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alexey_melnikov/32/23060_2.png) [@Alexey\_Melnikov](https://discuss.elastic.co/u/Alexey_Melnikov)\
**Post date:** [October 29, 2017, 9:08am UTC](https://discuss.elastic.co/t/distinct-collect-first-letters-from-field/105521/3 "2017-10-29T09:08:31Z")

</div>

Thanks Igor.

I think it's doable for me.

But I have another question: may be I can store that values in another type which will hold for example only 26 records? And on each index update I can intercept new values and update only one record in that type. In that case I don't need every time to search through all index to find 26 letters.

Of course it can be done outside of ES, but it will significantly increase the time of processing data, because I need first insert to index new value, wait for index update, execute search and find new first record for that letter (I need elasticsearch sorting because I'm trying to move all collation logic to ES from application, and sorting depends on settings of icu\_collation\_keyword).

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [October 29, 2017, 1:04pm UTC](https://discuss.elastic.co/t/distinct-collect-first-letters-from-field/105521/4 "2017-10-29T13:04:56Z")

</div>

Without support for transactions, implementing something like this in a reliable fashion would be quite difficult.

---

<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:** [November 26, 2017, 1:05pm UTC](https://discuss.elastic.co/t/distinct-collect-first-letters-from-field/105521/5 "2017-11-26T13:05:14Z")

</div>

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