Elasticsearch group by and distinct

Question

I have a bunch of documents with the fields username and device_os as follows:

{ "username": "foo", "device_os": "Android", ....},
{ "username": "foo", "device_os": "iOS", ....},
{ "username": "bar", "device_os": "Android", ....},
{ "username": "baz", "device_os": "iOS", ....},
{ "username": "foo", "device_os": "iOS", ....}

I would like to get all distinct device_os by username as follows:

{ 
  "foo": ["Android", "iOS"],
  "bar": ["Android"],
  "baz": ["iOS"] 
}

What is the best way to do something like this in elasticsearch, specifically elasticsearch-py?

score 0 · Answer 1 · answered Aug 26 '20 at 07:16

In case someone gets here, the idea to solve this problem is simple: use a second level aggregation on the usr_agg, something like this:

{
    "size": 0,
    "aggs": {
        "usr_agg": {
            "terms": {
                "field": "username.keyword"
            },
            "aggs": {
                "by_device_os": {
                    "terms": {
                        "field": "device_os.keyword"
                    }
                }
            }
        }
    }
}

this seems not working, the nest aggs not found. `by_device_os` not found exception — tim, Mar 15 '23 at 01:08

score -1 · Answer 2 · answered Aug 31 '16 at 19:45

-1

you can use "terms" aggregation in following manner

{
 "query": {
           "match_all": {}
          },
 "aggs":{
          "usr_agg":{
                     "terms": {"field": "username"}
                    }
        }
 }

For more info

answered Aug 31 '16 at 19:45

deepanshu gupta

75
2

1

But how do I get the distinct device_os per user? This just has a terms aggregator for the username field – Vineet Goel Aug 31 '16 at 19:53
As of now I don't know if there is a way. One hack is you can use nested aggs to again aggregate devise_os – deepanshu gupta Aug 31 '16 at 20:31

Elasticsearch group by and distinct

2 Answers2