Get group by and distinct count of values using other field in Elasticsearch

[Vinay_Ranjan]

I have an index having document structure as below -

{
  "key": ["10", "20"],
  "keywords": [
    {
      'case': 1,
      'word': 'abc' 
    },
    {
      'case': 2,
      'word': 'def'
    },
    {
      'case': 1
      'word': abcd
    }
  ]
}

I need to apply filter on key=10 & get the count of distinct words by each case accros the documents. There are 20 disinct cases, so this query will return is 20 buckets at max.

Filter Condition: key = 10

Result

[
    {
      'case': 1,
      'value': 2
    },
    {
      'case': 2,
      'value': 1
    }
  ]

Equivalent SQL Query:

select case, count(distinct words) as value 
from <table> where key = 10 and case in (1, 2, 3, 4) group by case;