how to implement the following sql query in es?

Question

I want to query similarly the following SQL query:

select countryName, count( distinct(countryId) ) as findCount   from city group by countryName having findCount > 1

Who know how to implement in es ?

thanks for your answer !

Answer 1

You'd do this with a terms aggregation with min_doc_count: 2 like this

{
  "size": 0,
  "aggs": {
    "countries": {
      "terms": {
        "field": "countryName"
      },
      "aggs": {
        "ids": {
          "terms": {
            "field": "countryId",
            "min_doc_count": 2
          }
        }
      }
    }
  }
}

Note that the countryName field should be not_analyzed in order for this to work, or the countryName field is a multi-field with a not_analyzed part.