Count distinct values using elasticsearch

Question

I am learning elastic search and would like to count distinct values. So far I can count values but not distinct.

Here is the sample data:

curl http://localhost:9200/store/item/ -XPOST -d '{
  "RestaurantId": 2,
  "RestaurantName": "Restaurant Brian",
  "DateTime": "2013-08-16T15:13:47.4833748+01:00"
}'

curl http://localhost:9200/store/item/ -XPOST -d '{
  "RestaurantId": 1,
  "RestaurantName": "Restaurant Cecil",
  "DateTime": "2013-08-16T15:13:47.4833748+01:00"
}'

curl http://localhost:9200/store/item/ -XPOST -d '{
  "RestaurantId": 1,
  "RestaurantName": "Restaurant Cecil",
  "DateTime": "2013-08-16T15:13:47.4833748+01:00"
}'

And what I tried so far:

curl -XPOST "http://localhost:9200/store/item/_search" -d '{
  "size": 0,
  "aggs": {
    "item": {
      "terms": {
        "field": "RestaurantName"
      }
    }
  }
}'

Output:

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 3,
    "max_score": 0.0,
    "hits": []
  },
  "aggregations": {
    "item": {
      "buckets": [
        {
          "key": "restaurant",
          "doc_count": 3
        },
        {
          "key": "cecil",
          "doc_count": 2
        },
        {
          "key": "brian",
          "doc_count": 1
        }
      ]
    }
  }
}

How can I get count of cecil as 1 instead of 2

score 14 · Answer 1 · edited Apr 30 '19 at 12:22

14

You have to use cardinality option as mentioned by @coder that you can find in the doc

$ curl -XGET "http://localhost:9200/store/item/_search" -d'
{
"aggs" : {
    "restaurant_count" : {
        "cardinality" : {
            "field" : "RestaurantName",
            "precision_threshold": 100, 
            "rehash": false 
            }
          }
         }
}'

This worked for me ...

edited Apr 30 '19 at 12:22

slm

15,396
12
109
124

answered Oct 04 '14 at 09:54

c24b

5,278
6
27
35

4

As correctly pointed by @c24b, `cardinality` serves the purpose here, but I would like to point out a few things here: 1. `cardinality` aggregation is an "approximate" algorithm based on [HyperLogLog++ (HLL)][http://static.googleusercontent.com/media/research.google.com/en//pubs/archive/40671.pdf] algorithm. Quoting from documentation: `HLL works by hashing your input and using the bits from the hash to make probabilistic estimations on the cardinality`. There is a trade-off between "precision" and "memory". – nishant kumar Aug 26 '19 at 13:26
For more details read here: https://www.elastic.co/guide/en/elasticsearch/guide/current/cardinality.html My apologies for citing the link as I was not able to explain more due to space constraints. – nishant kumar Aug 26 '19 at 13:39
3

Got this error with rehash option: `[7:23] [cardinality] rehash doesn't support values of type: VALUE_BOOLEAN` – Jerry Chin Jul 07 '22 at 08:14

score 5 · Answer 2 · answered Jul 10 '14 at 03:04

5

Use could use cardinality here: http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-metrics-cardinality-aggregation.html

answered Jul 10 '14 at 03:04

coder

1,901
5
29
44

I even tried : curl -XPOST "http://localhost:9200/store/item/_search" -d' {"size":0,"aggs":{"item":{"cardinality":{"field":"RestaurantName" } } } }' but not getting distinct counts – Developer Jul 10 '14 at 08:19
Should be curl -XGET? – c24b Oct 04 '14 at 09:55
and is it ok to rename the aggregate as item? – c24b Oct 04 '14 at 09:56

score 3 · Answer 3 · answered Jan 20 '21 at 16:07

It is too late for me to answer this question for the original Author, but for anybody who is facing the same issue and reached here, my answer might help.

ES provides Cardinality for sure to get distinct count, but it is not accurate. For accuracy, a proper solution can be used. I have written an article on this which might help : Accurate Distinct Count and Values from Elasticsearch.

score 0 · Answer 4 · answered Jun 12 '17 at 14:02

0

There's no support for distinct counting in ElasticSearch, although non-deterministic counting exists. Use "terms" aggregation and count buckets in result. See Count distinct on elastic search question.

answered Jun 12 '17 at 14:02

asu

539
6
15

score 0 · Answer 5 · answered Aug 17 '23 at 12:33

Use Cardinality Feature: Docs : https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-cardinality-aggregation.html

Example :

 "aggs": {
                "unquieValues": {
                  "cardinality": {
                    "field": "ourUniqueId.keyword",
                    "precision_threshold": 100
                  }
                }
              }

Count distinct values using elasticsearch

5 Answers5