From my testing, ARRAY_AGG() appears to be stable in the sense that it doesn’t reorder the input data. For example,

SELECT ARRAY_AGG(x) AS array_agg FROM UNNEST([20, 40, 50, 1, 2, 3]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [20, 40, 50, 1, 2, 3]   |
+-------------------------+

Notice the order of those elements is unchanged. Is this behavior guaranteed?

>Solution :

These things tend to be stable, but the order is not guaranteed.

From Aggregate function calls:

The ORDER BY clause is supported only for aggregate functions that depend on the order of their input. For those functions, if the ORDER BY clause is omitted, the output is nondeterministic.

It’s best to specify the order explicitly, for example:

ARRAY_AGG(x ORDER BY ABS(x))