Fetch unique records for multiple fields in elastic search version 6.2.3

Question

I am new to elastic search. Using 6.2.3 version of elastic search. I want elastic search equivalent of below SQL query:

SELECT DISTINCT customer_name , customer_services, customer_visible from customers;

I have around 200K documents in elastic search. I want unique records for multiple fields from a document. Those fields are customer_name , customer_services, customer_visible

I am not looking for a count, I want to fetch data for specified fields with no duplicate data for each fields.

I have tried few queries , but it not giving me unique for all fields. Please help me in providing elastic search 6.2.3 equivalent query for above sql.

Answer 1

Below is the working ES 6.3 equivalent query that worked for me.

{
  "size": 0,
  "aggs": {
    "company_details": {
      "terms": {
        "size": 10000,
        "script": "doc['customer_global_cust_id'].value + ' | ' + doc['customer_name'].value + ' | ' + doc['customer_visible'].value + ' | ' +doc['customer_services'].values"
      }
    }
  }
}

Above, I have used values for customer_services as it is array field in document, remaining field are just string value, hence value is used.

Answer 2

I am no expert, but as far as I know, if you wanted to do a select distinct on only one field, then you could have used Term Aggregation

But looking at this Post, Term aggregation on multiple fields is not supported in Elasticsearch, because it won't have good performance. You can look into the solution suggested in the same link about using script in term aggregation.