SQL DISTINCT Pair of Values in Elasticsearch

I want to ask how to use SQL DISTINCT Pair of Values in Elasticsearch . Like below,

SELECT distinct username, city from country_table

I can run below query for just one field. In the aggs json, I can only see "username" fields.

But I need DISTINCT username, city pair.

{
  "_source": ["username","city"]
  "aggs": {
     "unique_ids": 
        {
           "terms": { "fields": "username" }
        }
  }
}

How can i solve this ?

Thanks for answering

You should add a sub aggregation on the other field.

SELECT username, city FROM country_table GROUP BY username, city

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.