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.