Count distinct on elastic search

Question

How to achieve count distinct function on elastic search type using sql4es driver?

Select distinct inv_number , count(1) from invoices;

But it returns the total count of the particular invoice number.

score 12 · Answer 1 · answered Mar 19 '17 at 17:06

  {
      "size": 0, 
      "aggs": {
        "total_invoices": {
          "terms": {
            "field": "inv_number" 

        },
        "aggs": {
          "unique_invoiceid": {
            "cardinality": {
              "field": "inv_number"
            }
          }
        }
      }
    }

This will give you the invoice number as key and distict value in unique_invoiceid

score 7 · Answer 2 · edited Apr 30 '19 at 12:23

7

Elasticsearch doesn't support deterministic DISTINCT counts (source). It supports only approximate distinct counters like "cardinality". One way to count distincts in a deterministic way is to aggregate them using "terms" and count buckets from result.

edited Apr 30 '19 at 12:23

slm

15,396
12
109
124

answered Jun 12 '17 at 13:56

asu

539
6
15

2

Can you share the snippet for counting the number of buckets ? – Vaibhav Feb 05 '19 at 09:01

score 4 · Answer 3 · answered Mar 20 '17 at 02:56

4

Since, the OP is using sql4es jdbc driver, he is asking for a sql query for his use-case :

SELECT COUNT(DISTINCT inv_number) from invoices;

it returns the number of distinct values of the specified column

answered Mar 20 '17 at 02:56

Rahul

15,979
4
42
63

This worked for me in Kibana as an ESQL statement. Thanks – RAFJR Aug 23 '22 at 11:55

score 0 · Answer 4 · answered Jun 06 '19 at 17:51

0

This should work to count exact distinct values:

curl -X POST "localhost:9200/invoices/_search?size=0&pretty" -H 'Content-Type: application/json' -d '{
"aggs" : {
    "types_count" : {
      "value_count" : { "field" : "inv_number" }
    },
    "group_by_status": {
      "terms": {
        "field": "inv_number"
      }
    }
}

}'

answered Jun 06 '19 at 17:51

user582175

954
12
17

It does not count "distinct" values. – golopot Jul 16 '20 at 06:47

score 0 · Answer 5 · edited Oct 12 '21 at 07:42

0

Cardinality works if your dataset uniqueness/fragmentation is less than 2000. If you really care for accuracy instead of retrieval speed and memory utilization then you can go for scripted metric which will give you accurate results.

https://pratik-patil.medium.com/accurate-distinct-count-and-values-from-elasticsearch-dadce51ad4a7

edited Oct 12 '21 at 07:42

buddemat

4,552
14
29
49

answered Oct 12 '21 at 07:04

Ansh Nagrath

1
2

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 12 '21 at 07:42

Count distinct on elastic search

5 Answers5

Linked