# Querying the distinct values from ES index **URL:** \ **Category:** Elasticsearch\ **Created:** [January 2, 2019, 2:13pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666 "2019-01-02T14:13:05Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![chetansk](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@chetansk](https://discuss.elastic.co/u/chetansk)\ **Post date:** [January 2, 2019, 2:13pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/1 "2019-01-02T14:13:05Z")
Hi, I am using ES version 1.4.0. I would like to retrieve the unique values from a field in ES index which can be expressed in SQL format as follows: SELECT (DISTINCT UserName) FROM UsersTable; I have tried to use aggregations. I tried multiple solutions from different resources but couldn't succeed. Looking for suggestions. Thanks. ---
**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:** [January 2, 2019, 5:07pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/2 "2019-01-02T17:07:13Z")
First of all I would like to encourage you to upgrade ES ASAP. Besides being unsupported and unstable, 1.4.0 is simply not safe to use. > [@chetansk](#): > > I have tried to use aggregations. I tried multiple solutions from different resources but couldn't succeed. Aggregations is the way to go. Could you share more information about the issues that you have encountered? ---
**Author:** ![chetansk](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@chetansk](https://discuss.elastic.co/u/chetansk)\ **Post date:** [January 2, 2019, 5:40pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/3 "2019-01-02T17:40:29Z")
Hi @Igor_Motov, Thanks for the response. My query (in groovy) looks something like this: size:20, //1st size aggregations:[ agg\_name:[ terms:[ field:"Name", size:100000 //2nd size ] ] ] The query is not responding to the change in 2nd size. It is only responding to the 1st size. The result remains same even if i change the value of 2nd size. Also, the output value are not distinct (there are duplicates). ---
**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:** [January 2, 2019, 5:49pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/4 "2019-01-02T17:49:46Z")
Please set the first size to 0. I think you are looking at hits instead of looking at the aggregates. ---
**Author:** ![chetansk](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@chetansk](https://discuss.elastic.co/u/chetansk)\ **Post date:** [January 2, 2019, 5:52pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/5 "2019-01-02T17:52:44Z")
It is resulting into null output when i set the first size to 0. ---
**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:** [January 2, 2019, 5:53pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/6 "2019-01-02T17:53:55Z")
Can you send this command using curl and see if you are getting any output this way? Also check the elasticsearch log files. ---
**Author:** ![chetansk](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@chetansk](https://discuss.elastic.co/u/chetansk)\ **Post date:** [January 2, 2019, 5:57pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/7 "2019-01-02T17:57:17Z")
It seems working partially as the output is "Name". If i understood correctly, it won't output Name if aggregation is not working, right? Are the aggregates visible as output? I have multiple queries,second query consuming the output of first query. I am passing the hits of the first query as input to second. Is there some other way of passing the aggregates? ---
**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:** [January 2, 2019, 6:15pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/8 "2019-01-02T18:15:15Z")
> [@chetansk](#): > > It seems working partially as the output is "Name". If i understood correctly, it won't output Name if aggregation is not working, right? I am really not sure what you mean. > [@chetansk](#): > > Are the aggregates visible as output? I have multiple queries,second query consuming the output of first query. I am passing the hits of the first query as input to second. Is there some other way of passing the aggregates? You are describing something that elasticsearch doesn't do. So, I can only assume that you are using elasticsearch via some application in the front of the elasticsearch. So the answer to your question really depends on the application that you are using. ---
**Author:** ![chetansk](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@chetansk](https://discuss.elastic.co/u/chetansk)\ **Post date:** [January 2, 2019, 6:18pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/9 "2019-01-02T18:18:05Z")
Hi, Thanks for the response. I would like to ask if its possible to know whether aggregations is working fine in ES. ---
**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:** [January 2, 2019, 6:28pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/10 "2019-01-02T18:28:21Z")
If you don't get any errors and you see something like: ```auto { ... "aggregations" : { "The Name Of The Aggregation" : { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets" : [ ... ] } } } ``` in the output, it means it works. When you said it worked "partially", what did you mean? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 30, 2019, 6:28pm UTC](https://discuss.elastic.co/t/querying-the-distinct-values-from-es-index/162666/11 "2019-01-30T18:28:27Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.