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 !

Val · Accepted Answer · 2016-08-20 09:18:21Z

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.

edited Aug 20, 2016 at 9:18

answered Aug 20, 2016 at 3:56

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Qin Dong Liang Over a year ago

i have a count( distinct(countryId) ) function ,it seems need pipeline agge to having filter count

Val Over a year ago

There's a 1:1 relationship between countryId and countryName right? so count(distinct(countryId)) == count(distinct(countryName)) right?

Qin Dong Liang Over a year ago

my data count(distinct(countryId)) != count(distinct(countryName)) , so maybe use min_doc_count is incorrect

Val Over a year ago

So as I understand you have the same country name which can have different country ids? That's funny, but ok, I'll update my answer

Qin Dong Liang Over a year ago

min_doc_count instead min_doc_size can execute

Collectives™ on Stack Overflow

how to implement the following sql query in es?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related