MySQL Bugs: #94696: JSON_ARRAYAGG Sorting

Bug #94696	JSON_ARRAYAGG Sorting
Submitted:	18 Mar 2019 20:15	Modified:	19 Mar 2019 14:52
Reporter:	Mathieu de Lorimier	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S4 (Feature request)
Version:	8.0.15	OS:	Any
Assigned to:		CPU Architecture:	x86

Description:
Hello,

It seems JSON_ARRAYAGG is not respecting the ORDER BY clause and does not provide a way of ordering the aggregated array.

How to repeat:
With the following table

| ID | Number  |
----------------
|  1 |       4 |
|  2 |       5 |
|  3 |       3 |
|  4 |       6 |
|  5 |       8 |

The two following statements should give different outputs

SELECT JSON_ARRAYAGG(Number) FROM tblTest

SELECT JSON_ARRAYAGG(Number) FROM tblTest ORDER BY Number

Suggested fix:
The function should either respect the ORDER BY clause or provide a way to order it's results like the GROUP_CONCAT function.

HI,

Thank you for your contribution.

First of all, this function should be used only with aggregated queries, which imply GROUP BY.

However, your comparison with GROUP_CONCAT() makes sense, so this could make a good feature request.

Verified as a feature request.

Ok,

But in my opinion, the engine should respect the order when aggregating from a subquery like so :

SELECT JSON_ARRAYAGG(rSub.Nmber) FROM (SELECT * FROM tblTest ORDER BY Number) AS rSub

This makes it kind of confusing ...

Not necessarily, since ORDER BY in derived query can be optimised away.

Honouring the sort order would make another feature request. 

Hence, this report can be used for both features.

json_arrayagg is a great idea, but without "order by" is is almost useless.
In JSON there is only one thing that has a defined order - a JSON array. 
It is a rare case of wanting an array but not caring what order the elements are.

I have had to abandon use of JSON_ARRAYAGG and replace with messy concatenation of strings using group_concat( .. ORDER BY ..) and then converting into json.
Ugly, and slow!

Thank you for your contribution.

This will be added to our internal database.

Any news on this?

Sucks mysql still doesn't have this. Postgres <3

Hi,

Sadly, there are no news yet ......

Agree wholeheartedly...

"json_arrayagg is a great idea, but without "order by" is is almost useless."

Has this been scheduled for implementation?

Hi Mr. Lorimier,

Unfortunately, scheduling of the feature requests depends on the Product Management.

Their schedules are changing weekly and those changes can be dramatically different, so there is simply nobody who could possibly answer your question.