ORDER BY in JSON_ARRAYAGG()

[hooopo]

Feature Request

I found that TiDB already supports the json_arrayagg function, which can replace the old group_concat function in simple scenarios. But for scenes with sorting requirements, the json_arrayagg function cannot be satisfied.

CREATE TABLE test (
  id INT
);
INSERT INTO test (id) VALUES (3);
INSERT INTO test (id) VALUES (1);
INSERT INTO test (id) VALUES (2);

Use group_concat with order by

select group_concat(id order by id asc) as str from test group by id div 2;
+------+
| str |
+------+
| 2,3 |
| 1 |
+------+

TiDB json_arrayagg

select json_arrayagg(id order by id asc) as arr from test group by id div 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 29 near "order by id asc) as arr from test group by id div 2 "

without order by:

select json_arrayagg(id) as arr from test group by id div 2 ;
+--------+
| arr    |
+--------+
| [1]    |
| [3, 2] |
+--------+
--the order of the results is wrong

MariaDB 10.5

select json_arrayagg(id order by id asc) as arr from test group by id div 2;

+------+
| arr |
+------+
| [2,3] |
| [1] |
+------+

[chrysocolla]

I believe that TiDB is trying to be compatible with MySQL, not MariaDB?

As I was doing some investigation on implementing the missing JSON_VALUE built-in function for TiDB, I noticed there's no formal way for TiDB to handle optional clauses in function parameters at both parser and AST level. In your case, neither MySQL 8.0 or TiDB cares about the optional ORDER BY clauses for JSON_ARRAYAGG at parser level. MariaDB, Oracle and DB2 support that, but the same thing just doesn't hold true for MySQL or TiDB.

It might not be the most ideal thing to say, but given that to support this feature means to find a normalized way to build AST of function parameters with clauses, and the current TiDB behavior IS aligned with MySQL 8.0, perhaps the better way is to push MySQL a little bit?

best regards.

[hooopo]

it's supported in v5.2.1