Does anyone know if there is a way to achieve any sorting or filtering within the JSON_ARRAYAGG()?

https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_json-arrayagg says:

JSON_ARRAYAGG(col_or_expr)

Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.

https://bugs.mysql.com/bug.php?id=94696 is a feature request from 2019, asking for a way to sort elements before aggregating them with JSON_ARRAYAGG(). That ticket is in a "Verified" state, meaning it has been accepted as a feature request, but it has not been implemented.

The GROUP_CONCAT() aggregate function has an optional ORDER BY clause you can use inside the function call, but none of the other aggregate functions have such a clause.

You can take advantage of an unofficial method: because InnoDB tables read rows in index order according to the index used to access the table, you can use indexes to make it use a specific index.

Here's a table of test data:

mysql> select * from mytable;
+----+-------------+------+
| id | text        | x    |
+----+-------------+------+
|  1 | longing     | NULL |
|  2 | rusted      | NULL |
|  3 | seventeen   | NULL |
|  4 | daybreak    | NULL |
|  5 | furnace     | NULL |
|  6 | nine        | NULL |
|  7 | benign      | NULL |
|  8 | homecoming  | NULL |
|  9 | one         | NULL |
| 10 | freight car | NULL |
+----+-------------+------+

Here we see some words in my table are returned in alphabetical order:

mysql> select json_arrayagg(text) from mytable;
+-----------------------------------------------------------------------------------------------------------------+
| json_arrayagg(text)                                                                                             |
+-----------------------------------------------------------------------------------------------------------------+
| ["benign", "daybreak", "freight car", "furnace", "homecoming", "longing", "nine", "one", "rusted", "seventeen"] |
+-----------------------------------------------------------------------------------------------------------------+

The reason is that the query used an index on that text column as the access order. Because I only fetched one column, it was able to do an index-scan.

mysql> explain select json_arrayagg(text) from mytable;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | index | NULL          | text | 83      | NULL |   10 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+

Whereas if I include another non-indexed column, it does a table-scan, which reads rows in primary key order:

mysql> explain select x, json_arrayagg(text) from mytable;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

And the result is that the array is aggregated in primary key order, not in alphabetical order:

mysql> select x, json_arrayagg(text) from mytable;
+------+-----------------------------------------------------------------------------------------------------------------+
| x    | json_arrayagg(text)                                                                                             |
+------+-----------------------------------------------------------------------------------------------------------------+
| NULL | ["longing", "rusted", "seventeen", "daybreak", "furnace", "nine", "benign", "homecoming", "one", "freight car"] |
+------+-----------------------------------------------------------------------------------------------------------------+

Can I make another index to force it to use another order?

mysql> update mytable set x = 11 - id;
Query OK, 10 rows affected (0.03 sec)

mysql> select * from mytable order by id;
+----+-------------+------+
| id | text        | x    |
+----+-------------+------+
|  1 | longing     |   10 |
|  2 | rusted      |    9 |
|  3 | seventeen   |    8 |
|  4 | daybreak    |    7 |
|  5 | furnace     |    6 |
|  6 | nine        |    5 |
|  7 | benign      |    4 |
|  8 | homecoming  |    3 |
|  9 | one         |    2 |
| 10 | freight car |    1 |
+----+-------------+------+

mysql> alter table mytable add key (x, text);

The EXPLAIN shows that it will prefer to do an index-scan if I only reference the two columns in that index:

mysql> explain select x, json_arrayagg(text) from mytable;                                                                                                              
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | index | NULL          | x    | 88      | NULL |   10 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+

And because x is first in this index, that determines the order in which the rows are aggregated.

mysql> select x, json_arrayagg(text) from mytable;
+------+-----------------------------------------------------------------------------------------------------------------+
| x    | json_arrayagg(text)                                                                                             |
+------+-----------------------------------------------------------------------------------------------------------------+
|    1 | ["freight car", "one", "homecoming", "benign", "nine", "furnace", "daybreak", "seventeen", "rusted", "longing"] |
+------+-----------------------------------------------------------------------------------------------------------------+

If you have more columns, or if the table is a joined table that is not the first in the join order, it may be quite tricky—or even impossible—to get the array order the way you want, while still accessing the table with good optimization.