Querying the distinct values from ES index

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.

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.

Aggregations is the way to go. Could you share more information about the issues that you have encountered?

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).

Please set the first size to 0. I think you are looking at hits instead of looking at the aggregates.

It is resulting into null output when i set the first size to 0.

Can you send this command using curl and see if you are getting any output this way? Also check the elasticsearch log files.

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?

I am really not sure what you mean.

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.

Hi,

Thanks for the response. I would like to ask if its possible to know whether aggregations is working fine in ES.

If you don't get any errors and you see something like:

{
    ...

    "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?

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