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;