How to get exact unique doc count based on some fields

[sandeep_rana (Sandeep)]

Any way to find exact unique document count ..

based on some fields..

I have also tried cardinality its not working as expected

[elasticforme (Sachin)]

I use sql, for example

select username from index_name group by username

[sandeep_rana (Sandeep)]

Thank you Sachin ,
How we can do in elasticsearch .. ???

[Christian_Dahlqvist]

How much data are you querying? What is the expected cardinality you are measuring? What is the use case?

[sandeep_rana (Sandeep)]

Hi ,

I have 100K documents on one index pattern like (localhost_com_1005_do_br_scs_618229232191937012_8662_*). There is one field called id , so just need unique document count based on id.keyword.

Because may be chances to get duplicate while querying with index pattern ..

[Christian_Dahlqvist]

How many of indices and shards does that correspond to? What is the total size on disk?

[sandeep_rana (Sandeep)]

There is only one shard no replica . 200GB is disk size.

[elasticforme (Sachin)]

@sandeep_rana

may be chances to get duplicate while quer

I go to kibana - dev tool

GET _sql?format=txt
{
  "query": """
        select count(*), id
    from "localhost_com_1005_do_br_scs_618229232191937012_8662_*" 
        group by id
        """
}   

Problem you might face is that id is also a elasticsearch field.

use following to see what exactly field name listed in elasticsearch

GET sql?format=txt
{
"query": """
desc "localhost_com_1005_do_br_scs_618229232191937012_8662*"
"""
}