Find distinct values, not distinct counts in elasticsearch

Question

Elasticsearch documentation suggests* that their piece of code

*documentation fixed

GET /cars/transactions/_search?search_type=count {   "aggs": {     "distinct_colors": {       "cardinality": {         "field": "color"       }     }   } } 

corresponds to sql query

SELECT DISTINCT(color) FROM cars 

but it actually corresponds to

SELECT COUNT(DISTINCT(color)) FROM cars 

I don't want to know how many distinct values I have but what are the distinct values. Anyone knows how to achieve that?

Answer 1

Use a terms aggregation on the color field. And you need to pay attention to how that field you want to get distinct values on is analyzed, meaning you need to make sure you're not tokenizing it while indexing, otherwise every entry in the aggregation will be a different term that is part of the field content.

If you still want tokenization AND to use the terms aggregation you might want to look at not_analyzed type of indexing for that field, and maybe use multi fields.

Terms aggregation for cars:

GET /cars/transactions/_search?search_type=count {   "aggs": {     "distinct_colors": {       "terms": {         "field": "color",         "size": 1000       }     }   } } 

Answer 2

To update the excellent answer from Andrei Stefan, we need to say that the query parameter search_type=count is no more supported in Elasticsearch 5. The new way of doing this is to add "size" : 0 in the body such as :

GET /cars/transactions/_search {   "size": 0,   "aggs": {     "distinct_colors": {       "terms": {         "field": "color",         "size": 1000       }     }   } }