I am new to Elastic Search, I want to create a query in the elastic search which works like a group by SQL. Here is my SQL query
SELECT top 100 *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY columnA
ORDER BY columnB) AS StRank, *
FROM table where columnA in ('a','b','c') ) n
WHERE StRank IN (1)
GO
I want a single row group by columnA and Order by columnB
You can use collapse.
Allows to collapse search results based on field values. The collapsing is done by selecting only the top sorted document per collapse key
{
"query": {
"bool": {
"filter": {- -> filter doesn't calculate score(if score needed use must)
"terms": {
"columnA": ["a","b","c"]
}
}
}
},
"collapse": { --> top 1 Group by column A
"field": "columnA"
},
"sort": [ --> sort
{
"columnB": {
"order": "desc"
}
}
]
}