CODEWITHSUNDEEP
elasticsearchdistinct
jasiustasiu
jasiustasiu

Reputation: 1668

Find distinct values, not distinct counts in elasticsearch

Elasticsearch documentation suggests* that their piece of code

*documentation fixed

GET /cars/transactions/_search?search_type=count
{
  "aggs": {
    "distinct_colors": {
      "cardinality": {
        "field": "color"
      }
    }
  }
}

corresponds to sql query 

SELECT DISTINCT(color) FROM cars

but it actually corresponds to 

SELECT COUNT(DISTINCT(color)) FROM cars

I don't want to know how many distinct values I have but what are the distinct values. Anyone knows how to achieve that?

Upvotes: 57

Views: 122161

Answers (4)

CODE-REaD
CODE-REaD

Reputation: 3058

While I appreciate the idea of leveraging Kibana to answer this question, I was unable to accomplish it in exactly the way described by @Phlucious. Here is how I proceeded (Kibana and Elasticsearch 7.8.1):

  1. Open Kibana's main Discover tool: main Kibana Discover menu location and click its Add filter link to narrow your search as much as possible;

  2. In Kibana's Available fields side-menu, left-click on the field you wish to extract distinct values of (in my case, data.vulnerability.package.condition):Kibana Available fields menu

  3. This will open a menu containing the top 5 values of this field, followed by a button labelled Visualize. Click on Visualize to open a visualization of the top values of your field:visualization

  4. Left-click the Inspect link above this chart. A sub-screen opens in which you may view the top values for your selected field:sub-screen

  5. In the upper right-hand corner of this sub-screen find a link labelled View: Data. Left-click it to choose Requests instead. In the header of the new sub-screen which appears you may click Request to access the Elasticsearch query Kibana used to generate your graph and chart:requests

Note that the value for size in my Request as shown in the image above is 100; initially it was 20, which is Kibana's default setting for Number of terms. I changed it to 100 in Kibana's Advanced Settings screen:Number of terms setting

Upvotes: 3

Phlucious
Phlucious

Reputation: 3843

Personally, both of the answers were arcane to me and hopelessly complex when I wanted to add multiple filters.

For me, what made sense was to go on the Discover tab and apply the filters I wanted. I then saved my search.

Then, I created a new Bar Chart visualization using my saved search. I then modified the X-Axis to use Terms aggregation based on my field of interest (in my case, Usernames), and then order by Count. Make sure the Size is something large, like 500.

You should be able to get the results in tabular form underneath your chart. Simple, and no complex JSON programming. Just a series of clicks. You can even save the visualization for later.

Upvotes: 3

Ortomala Lokni
Ortomala Lokni

Reputation: 62653

To update the excellent answer from Andrei Stefan, we need to say that the query parameter search_type=count is no more supported in Elasticsearch 5. The new way of doing this is to add "size" : 0 in the body such as : 

GET /cars/transactions/_search
{
  "size": 0,
  "aggs": {
    "distinct_colors": {
      "terms": {
        "field": "color",
        "size": 1000
      }
    }
  }
}

Upvotes: 54

Andrei Stefan
Andrei Stefan

Reputation: 52368

Use a terms aggregation on the color field. And you need to pay attention to how that field you want to get distinct values on is analyzed, meaning you need to make sure you're not tokenizing it while indexing, otherwise every entry in the aggregation will be a different term that is part of the field content.

If you still want tokenization AND to use the terms aggregation you might want to look at not_analyzed type of indexing for that field, and maybe use multi fields.

Terms aggregation for cars:

GET /cars/transactions/_search?search_type=count
{
  "aggs": {
    "distinct_colors": {
      "terms": {
        "field": "color",
        "size": 1000
      }
    }
  }
}

Upvotes: 56

Related Questions