# DISTINCT values DSL query

**URL:** <https://discuss.elastic.co/t/distinct-values-dsl-query/302715>\
**Category:** Elasticsearch\
**Created:** [April 19, 2022, 12:18pm UTC](https://discuss.elastic.co/t/distinct-values-dsl-query/302715 "2022-04-19T12:18:57Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![adrianfusco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adrianfusco/32/111798_2.png) [@adrianfusco](https://discuss.elastic.co/u/adrianfusco)\
**Post date:** [April 19, 2022, 12:18pm UTC](https://discuss.elastic.co/t/distinct-values-dsl-query/302715/1 "2022-04-19T12:18:57Z")

</div>

Hello,

I've been reading a lot about this topic because I've seen it has been asked before but I can do it works yet.

I am trying to get unique values from an index.

I have something like this:

```auto
id | app_name | url
1 | app_1 | https://subdomain.app_1.com
2 | app_1 | https://app_1.com
3 | app_2 | https://app_1.com
4 | app_3 | https://subdomain.app_3.com
5 | app_1 | https://app_3.com

```

I would like to receive just the distinct `app_name`:

```auto
app_1
app_2
app_3

```

The query I tried with `aggs` is:

```auto
GET app_index/_search
{
  "aggs": {
    "unique_apps": {
      "terms": {
        "field": "app_name",
      }
    }
  }
}

```

I also tried a kind of group by here:

```auto
GET app_index/_search
{
  "aggs": {
    "unique_apps": {
      "terms": {
        "field": "app_name.keyword"
      },
      "aggs": {
        "oneRecord": {
          "top_hits": {
            "size": 1
          }
        }
      }
    }
  }
}

```

But I still receive all the apps.

- Is there a way to receive unique values?
- Maybe is there a possibility to check in `logstash` if some value exists in the database and avoid sending it again? Or maybe use the `fingerprint` plugin and generate an unique `_id` according to the value of the field? If I receive the same information in that field it could generate the same ID so it won't be saved again.

I also checked if there's any possibility to create unique fields in Elasticsearch but I see it's not possible.

Thank you very much for your help and time 🙂

---

<div class="post-metadata">

**Author:** ![aaron-nimocks](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aaron-nimocks/32/73965_2.png) [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)\
**Post date:** [April 19, 2022, 11:11pm UTC](https://discuss.elastic.co/t/distinct-values-dsl-query/302715/2 "2022-04-19T23:11:02Z")

</div>

> [@adrianfusco](#):
>
> ```auto
> GET app_index/_search
> {
> "aggs": {
> "unique_apps": {
> "terms": {
> "field": "app_name",
> }
> }
> }
> }
> 
> ```

This one is correct. I think you are seeing in the results all the `hits` which is the records returned from the query. If you scroll to the bottom of the return you should see the aggregation. Most of the time when doing aggregation you don't need the hits so you can remove them using the below and it will only return the aggs.

```auto
GET app_index/_search
{
  "size": 0,
  "aggs": {
    "unique_apps": {
      "terms": {
        "field": "app_name",
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![adrianfusco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adrianfusco/32/111798_2.png) [@adrianfusco](https://discuss.elastic.co/u/adrianfusco)\
**Post date:** [April 20, 2022, 3:05pm UTC](https://discuss.elastic.co/t/distinct-values-dsl-query/302715/3 "2022-04-20T15:05:32Z")

</div>

Thank you for your answer @aaron-nimocks

In this case I see that there are some values in the `buckets` list inside the aggregations but unfortunately not the data.

At the end I created another index with unique values according to the string using the `fingerprint` plugin. I'm not very sure if it's the best option but at the end I need to extract a lot of information and it was taking a lot of time.

[I'll share what I did](https://stackoverflow.com/questions/71930364/distinct-values-dsl-query), maybe can be helpful to someone else that want to do the something similar.

* * *

* * *

- Is there a way to receive unique values?

I've used the `fingerprint` plugin in this case. I've generated an unique ID based on the string. e.g, if I receive the same `app_name` name it will generate always the same `_id` so it won't be repeated in Elasticsearch. I've added this config in the `logstash.conf` pipeline, specifically in the `filter` side:

```auto
fingerprint {
    source => ["app_name"]
    target => ["unique_id_by_app_name"]
    method => "SHA1"
  }

```

Then in the `output`:

```auto
    elasticsearch {
      hosts => "localhost:9200"
      index => "logstash_apps"
      document_id => "%{[unique_id_by_app_name]}"
    }

```

If I receive again the `app_1` with the same or even different data I'll have the same ID because the hashing:

```auto
$ -> echo -n "app_1" | sha1sum | awk -F ' -' '{print $1}'
87dbad46d7c47f3714eb02ff70e18b94e4ee6523

```

---

<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:** [May 18, 2022, 3:06pm UTC](https://discuss.elastic.co/t/distinct-values-dsl-query/302715/4 "2022-05-18T15:06:18Z")

</div>

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