Elasticsearch return unique values for a field

Question

I am trying to build an Elasticsearch query that will return only unique values for a particular field.

I do not want to return all the values for that field nor count them.

For example, if there are 50 different values currently contained by the field, and I do a search to return only 20 hits (size=20). I want each of the 20 results to have a unique result for that field, but I don't care about the 30 other values not represented in the result.

For example with the following search (pseudo code - not checked):

{
    from: 0,
    size: 20,
    query: {
        bool: {
            must: {
                range: { field1: { gte: 50 }},
                term: { field2: 'salt' },

                /**
                * I want to return only unique values for "field3", but I
                * don't want to return all of them or count them.
                *
                * How do I specify this in my query?
                **/
                unique: 'field3',
            },
            mustnot: {
                match: { field4: 'pepper'},
            }
        }
    }
}

Answer 1

I'm very surprised a filter aggregation hasn't been suggested. It goes back all the way to ES version 1.3.

The filter aggregation is similar to a regular filter query but can instead be nested into an aggregation chain to filter out counts of documents that don't meet a particular criteria and give you sub-aggregation results based only on the documents that meet the criteria of the query.

First, we'll put our mapping.

curl --request PUT \
  --url http://localhost:9200/items \
  --header 'content-type: application/json' \
  --data '{
  "mappings": {
    "item": { 
      "properties": { 
        "field1" :    { "type": "integer"  },
        "field2" :    { "type": "keyword"  },
        "field3" :    { "type": "keyword"  },
        "field4" :    { "type": "keyword"  }
      }
    }
  }
}
'

Then let's load some data.

curl --request PUT \
  --url http://localhost:9200/items/_bulk \
  --header 'content-type: application/json' \
  --data '{"index":{"_index":"items","_type":"item","_id":1}}
{"field1":50, "field2":["salt", "vinegar"], "field3":["garlic", "onion"], "field4":"paprika"}
{"index":{"_index":"items","_type":"item","_id":2}}
{"field1":40, "field2":["salt", "pepper"], "field3":["onion"]}
{"index":{"_index":"items","_type":"item","_id":3}}
{"field1":100, "field2":["salt", "vinegar"], "field3":["garlic", "chives"], "field4":"pepper"}
{"index":{"_index":"items","_type":"item","_id":4}}
{"field1":90, "field2":["vinegar"], "field3":["chives", "garlic"]}
{"index":{"_index":"items","_type":"item","_id":5}}
{"field1":900, "field2":["salt", "vinegar"], "field3":["garlic", "chives"], "field4":"paprika"}
'

Notice, that only the documents with id's 1 and 5 will pass the criteria and so we will be left to aggregate on these two field3 arrays and four values total. ["garlic", "chives"], ["garlic", "onion"]. Also notice that field3 can be an array or single value in the data but I'm making them arrays to illustrate how the counts will work.

curl --request POST \
  --url http://localhost:9200/items/item/_search \
  --header 'content-type: application/json' \
  --data '{
    "size": 0,
    "aggregations": {
        "top_filter_agg" : {
            "filter" : { 
                "bool": { 
                    "must":[
                        {
                            "range" : { "field1" : { "gte":50} }
                        },
                        {
                            "term" : { "field2" : "salt" }
                        }
                    ],
                    "must_not":[
                        {
                            "term" : { "field4" : "pepper" }
                        }
                    ]
                } 

            },
            "aggs" : {
                "field3_terms_agg" : { "terms" : { "field" : "field3" } }
            }
        }
    }
}
'

After running the conjuncted filter/terms aggregation. We only have a count of 4 terms on field3 and three unique terms altogether.

{
    "took": 46,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 5,
        "max_score": 0.0,
        "hits": []
    },
    "aggregations": {
        "top_filter_agg": {
            "doc_count": 2,
            "field3_terms_agg": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                    {
                        "key": "garlic",
                        "doc_count": 2
                    },
                    {
                        "key": "chives",
                        "doc_count": 1
                    },
                    {
                        "key": "onion",
                        "doc_count": 1
                    }
                ]
            }
        }
    }
}

Answer 2

You should be able to do this pretty easily with a terms aggregation.

Here's an example. I defined a simple index, containing a field that has "index": "not_analyzed" so we can get the full text of each field as a unique value, rather than terms generated from tokenizing it, etc.

DELETE /test_index

PUT /test_index
{
   "settings": {
      "number_of_shards": 1
   },
   "mappings": {
      "doc": {
         "properties": {
            "title": {
               "type": "string",
               "index": "not_analyzed"
            }
         }
      }
   }
}

Then I add a few docs with the bulk API.

POST /test_index/_bulk
{"index":{"_index":"test_index","_type":"doc","_id":1}}
{"title":"first doc"}
{"index":{"_index":"test_index","_type":"doc","_id":2}}
{"title":"second doc"}
{"index":{"_index":"test_index","_type":"doc","_id":3}}
{"title":"third doc"}
{"index":{"_index":"test_index","_type":"doc","_id":4}}
{"title":"third doc"}

Now we can run our terms aggregation:

POST /test_index/_search?search_type=count
{
   "aggs": {
      "unique_vals": {
         "terms": {
            "field": "title"
         }
      }
   }
}
...
{
   "took": 1,
   "timed_out": false,
   "_shards": {
      "total": 1,
      "successful": 1,
      "failed": 0
   },
   "hits": {
      "total": 4,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "unique_vals": {
         "buckets": [
            {
               "key": "third doc",
               "doc_count": 2
            },
            {
               "key": "first doc",
               "doc_count": 1
            },
            {
               "key": "second doc",
               "doc_count": 1
            }
         ]
      }
   }
}