Order by aggregate fields question

n1ql

#1

the following N1QL works:

SELECT PreparedName,avg(ResultCount) FROM system:completed_requests 
where PreparedName is not missing group by PreparedName order by avg(ResultCount);

but the following N1QL:

SELECT PreparedName,array_agg({"ResultCount":ResultCount,"ResultSize":ResultSize}) as Result FROM system:completed_requests 
where PreparedName is not missing group by PreparedName order by PreparedName,ResultCount,ResultSize;

don’t work.

I want to aggregate ResultCount field group by PreparedName, and order by PreparedName first,and then order by ResultCount in Result array,last order by ResultSize in the Result array .

I also try this N1QL:

SELECT PreparedName, array_sort(array_agg({"ResultCount":ResultCount,"ResultSize":ResultSize}))  FROM system:completed_requests 
where PreparedName is not missing group by PreparedName order by PreparedName;

and I found the sort order is depend on the order of array_agg (ResultCount > ResultSize) Am I right?


#2

If I want to order by some fields desc. The following N1QL might work:

SELECT PreparedName, array_reverse(array_sort(array_agg({"ResultCount":ResultCount,"ResultSize":ResultSize})))   FROM system:completed_requests 
where PreparedName is not missing group by PreparedName order by PreparedName;

Is there a better way?


#3

Only the first query will work.

When you use a GROUP BY, only two kinds of data are available for SELECT and ORDER BY:

(1) GROUP BY keys (e.g. PreparedName in your example)

(2) aggregate functions (e.g. AVG and ARRAY_AGG in your example).

You cannot use any other type of expression in SELECT or ORDER BY.