What is equivalent of sql count(dictinct())) in elasticsearch?

Question

I want to ask that what is equivalent of sql count(dictinct())) in elasticsearch ? I mean how to make the below sql query to elasticsearch query. I tried some aggregation but i didn't find the exact answer.

select count(distinct(unique_field)) where db_date < "..." and db_date > "..."

I have tried the below code but it doesn't give the expected result via sql.

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "city": {
              "value": "bratislava"
            }
          }
        },
        {
          "range": {
            "db_date": {
              "gte": "2020-05-15T00:00:00.000Z",
              "lte": "2020-05-16T00:00:00.000Z"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "types_count": {
      "value_count": {
        "field": "unique_field"
      }
    },
    "group_by_status": {
      "terms": {
        "field": "unique_field"
      }
    }
  }
}

Thanks for answering

Does this answer your question? [Count distinct on elastic search](https://stackoverflow.com/questions/42885532/count-distinct-on-elastic-search) — Joe - GMapsBook.com, Feb 25 '21 at 16:38
It returns multiple unique_field docs. But I want exactly like output of the sql — Orkun, Feb 25 '21 at 17:01
I mean the expected result should have just unique_field distinct count. The expected result is same result with sql. But this returns docs in hits that have same unique field with all the field of the doc — Orkun, Feb 25 '21 at 17:28
I see. You can set `"size": 0` on the top level of your query to just obtain the aggregation result(s). Your original question was looking for the `count`, not for the individual docs :) — Joe - GMapsBook.com, Feb 25 '21 at 17:30
Yes thats right but one thing, I was reaching the datas with scroll api in python using data['hits']['hits'] , is it possible to use data['aggregations']['group_by_status']['buckets'] in scroll api? — Orkun, Feb 25 '21 at 17:32

What is equivalent of sql count(dictinct())) in elasticsearch?

0 Answers0