How to do group by multiple fields and sort on a different field, in elasticsearch

#1

Lets say I have a query like

Select * from table
Group By field_1, field_2, field_3
Order By filed_7;

How to achieve the same functionality in elasticsearch.

I use Composite Aggregations to do Group by multiple fields. Is it possible to do the sorting part?

#2

With Composite Aggregations you can only sort by the grouped by fields (you can only change to descending order if you wish).

But just to clarify, why do you want to ORDER BY a field that it's not in the group by or the select list?
This query is not a valid SQL query, PostgreSQL for example will return:

column "field_7" must appear in the GROUP BY clause or be used in an aggregate function
#3

That query was just to give an idea in query terms. And thanks for the input.

#4

Composite aggs through ES API don't allow to group by anything else than the group by fields
ES SQL API though allows you to also order on aggregate function:

e.g.:

SELECT field_1, count(*) as cnt
FROM test
GROUP BY field_1
ORDER BY cnt
#5

Thanks @matriv. We are using elasticsearch hosted on AWS, so I afraid I cant use ES SQL API for my purpose. But good to know. I appreciate it