What is the SQL “Distinct” equivalent in Kibana?

The following "terms aggregation" should work if you could run it on Kibana > DevTools > Console:

GET /_search
{
    "size": 0,
    "aggs" : {
        "distinct_serviceprovidername" : {
            "terms" : { "field" : "serviceprovidername" }
        }
    }
}

The query returns a multi-bucket value source including distinct values of serviceprovidername field like this:

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 3,
    "successful" : 3,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 15601,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "distinct_serviceprovidername" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 67,
      "buckets" : [
        {
          "key" : "X",
          "doc_count" : 301
        },
        {
          "key" : "Y",
          "doc_count" : 207
        },
        {
          "key" : "Z",
          "doc_count" : 175
        }
      ]
    }
  }
}

Edit: "size": 0 is used when aggregations are required but search hits are not. In this occasion, fetching search hits does not make any sense, so I ignored it to make the request more efficient by returning only aggregation results.

In my case it worked perfectly with wild card search and regex in it.

This is how my data looks Tags:All Docs,Domains,ToAnalyse,Unique

I applied filters on Tags columns and get unique count.

Below is my query:

GET test1/_search
{
  "aggs": {
    "7772": {
      "filters": {
        "filters": {
          "Unique": {
            "query_string": {
              "query": "Tags:Unique",
              "analyze_wildcard": true,
              "default_field": "*"
            }
          },
          "ToAnalyse": {
            "query_string": {
              "query": "Tags:To.*",
              "analyze_wildcard": true,
              "default_field": "*"
            }
          },
          "All Docs": {
            "query_string": {
              "query": "Tags:All Docs",
              "analyze_wildcard": true,
              "default_field": "*"
            }
          },
          "Outdated": {
            "query_string": {
              "query": "Tags:Outdated",
              "analyze_wildcard": true,
              "default_field": "*"
            }
          },
          "Domains": {
            "query_string": {
              "query": "Tags:Domains",
              "analyze_wildcard": true,
              "default_field": "*"
            }
          }
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  }
}

And my output :

{
  "took" : 12,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 152636,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "7772" : {
      "buckets" : {
        "All Docs" : {
          "doc_count" : 152636
        },
        "Domains" : {
          "doc_count" : 43374
        },
        "Outdated" : {
          "doc_count" : 24322
        },
        "ToAnalyse" : {
          "doc_count" : 128260
        },
        "Unique" : {
          "doc_count" : 152636
        }
      }
    }
  }
}

Hope this helps :)

I didn’t find a “distinct “ kind of things in Kibana. However I’m able to plot this on x-y axis as suggested.Thanks for the help.